Database attachments query slow (System Files)

[ericp-mrel]

So, I have almost 2 million records in my system_files table and when I retrieve all of the images that are associated with a given model it takes between 8 - 9 seconds just to query the system_files table.

Is there a way to better optimize the indexes or something? Because I noticed it is only using the system_files_field_index and MySQL still ends up needing to filter through 908975 rows

Here's the query that is generated by Laravel:

select *
from `system_files`
where `system_files`.`attachment_id` = 100962
  and `system_files`.`attachment_type` = 'Mrelevance\RealEstate\Models\Listing'
  and `field` = 'images'
order by `sort_order` ASC

Here's the SQL EXPLAIN output:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	system_files	ref	system_files_attachment_id_index,system_files_attachment_type_index,system_files_field_index	system_files_attachment_type_index	767	const	908975	100	Using index condition; Using where; Using filesort

It seems like generating a compound index definitely helps speed up the query, but still isn't as fast as I would like:

create index system_files_field_attachment_type_attachment_id_index
    on system_files (field, attachment_type, attachment_id);

EDIT:
It looks like creating the following index seems to produce the best results for me. I seem to be averaging around 500 - 700 ms query time

create index system_files_index
    on system_files (field, sort_order, attachment_type, attachment_id);

Any ideas on how to further optimize this?

Thanks!

[LukeTowers]

I think a big part of the problem for system_files performance with large number of records is the fact that the attachment_id column is a VARCHAR instead of an unsigned integer in order to support records with string keys, so you could try changing the column type.

[rubenvanerk]

Why is it a VARCHAR anyway? The docs also suggest using integers for polymorphic relations.

Could/should that be changed in the Winter source code?

[LukeTowers]

It's theoretically a breaking change, which is why it hasn't happened yet, but I imagine the migration could be written to verify that all current values are integers before changing the column type and then explicitly document the fact that by default we only allow connects to records with integer keys for system_files records (perhaps also with instructions to change that on a per project basis).

[ericp-mrel]

@LukeTowers I can confirm re-creating the indexes into a composite index with the following order field, attachment_type, attachment_id and converting the attachment_id field to int drastically improves performance when dealing with around 2 million records.

My query times have gone from 9s to a max of around 60ms with the composite key and converting attachment_id to int. Keeping the attachment_id as VARCHAR the query times are around 3 - 5 seconds, but the composite key at least cuts the query time in half worst case.

Would you at least be interested in a patch to remove the current indexes and re-create them as a composite index?
An example of what I've tested with:

<?php

use Winter\Storm\Database\Schema\Blueprint;
use Winter\Storm\Database\Updates\Migration;
use Winter\Storm\Support\Facades\Schema;

class DbImproveSystemFilesIndexes extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('system_files', function (Blueprint $table) {
            $table->dropIndex(['field']);
            $table->dropIndex(['attachment_id']);
            $table->dropIndex(['attachment_type']);

            $table->index([
                'field',
                'attachment_type',
                'attachment_id',
            ]);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('system_files', function (Blueprint $table) {
            $table->dropIndex([
                'field',
                'attachment_type',
                'attachment_id',
            ]);

            $table->index('field');
            $table->index('attachment_id');
            $table->index('attachment_type');
        });
    }
}

Would love to see the option of making the attachment_id field type INT since it is a night and day difference in terms of performance when dealing with large amounts of records.

[LukeTowers]

@ericp-mrel I'd be happy with that. If you'd like, you can also add in a change from varchar to unsigned bigInt after performing a scan of the current DB content to make sure changing the column type won't affect any existing data in the table.