Order by date always uses filesort

Question

I have the following table definition:

CREATE TABLE `test` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `number` INT(10) UNSIGNED NOT NULL,
    `revision` INT(10) UNSIGNED NOT NULL,
    `autosave` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `blob` LONGBLOB NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `number_revision_autosave` (`number`, `revision`, `autosave`) USING BTREE,

    -- The following was not there originally but my attempt to solve my own issue
    UNIQUE INDEX `number_revision_autosave_created_at` (`number`, `revision`, `autosave`, `created_at`) USING BTREE 
);

the blob column can potentially be large(ish). On average it's about 20kb but can be much larger (there's no real upper limit, but a practical limit of 20-30mb can be assumed).

When I use a query like this:

select *
from test
WHERE `number` = 1 
    AND autosave=0
order by `revision` desc

everything is working great

However if I use:

select *
from test

-- This is also my attempt at a solution
USE INDEX (number_revision_autosave_created_at)

WHERE `number` = 1 
    AND autosave=0
order by `created_at` desc

I sometimes get Error "1038 Out of sort memory, consider increasing server sort buffer size

After a bit of looking into it the notable difference is that the explain of the 1st query is showing: Using where; Backward index scan in the extras while the second one is showing Using index condition; Using filesort

The "out of sort memory error" started making more sense at this point because my sort buffer size was 256kb and the result set contained single rows that were larger than this.

In my attempt to solve this I created an index which included created_at i.e.

UNIQUE INDEX `number_revision_autosave_created_at` (`number`, `revision`, `autosave`, `created_at`) USING BTREE 

Then tried to "persuade" MySQL to use that one by doing USE INDEX (number_revision_autosave_created_at) however, while MySQL did actually use this index, it still opted to do a filesort.

For now I have increased to sort buffer size to about 30mb, to cover these edge cases, but this is not an ideal (or future proof) solution.

I am not sure what I can do to make it use an index scan like in the previous case. What is the problem here? Default table engine is InnoDB and I am using MySQL Community version 8.0.32

Answer 1

The order of columns in an index matters as mysql can only proced to use columns starting from left one after the other.

Your indexes include revision column at the 2nd position from left, but this column is not included in the where clause at the 2nd position. This means, your indexes can only be used by the sample queries to filter on the number column.

Ad an index with this exact order of columns to speed the queries up in your question: number - autosave - created_at