MySQL seems to ignore single column index for composite index when sorting by primary key

Question

If I have a table such as

CREATE TABLE foo
(
    A TINYINT PRIMARY KEY AUTO_INCREMENT,
    B TINYINT NOT NULL,
    C TINYINT NOT NULL,
    D TINYINT NOT NULL,
    INDEX (B),
    INDEX (C),
    INDEX (D),
    INDEX (B, C, D)
);
);

If I then write a query such as

SELECT * FROM foo
WHERE B = 1
ORDER BY A DESC;

With certain data sets, MySQL seems to default to using filesort instead of just using the single column index on column B.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	foo	ref	B,B_2	B_2	1	const	2	100	Using where; Using index; Using filesort

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Index_type
foo	0	PRIMARY	1	A	A	4	NULL	NULL	BTREE
foo	1	B	1	B	A	4	NULL	NULL	BTREE
foo	1	C	1	C	A	4	NULL	NULL	BTREE
foo	1	D	1	D	A	4	NULL	NULL	BTREE
foo	1	B_2	1	B	A	4	NULL	NULL	BTREE
foo	1	B_2	2	C	A	4	NULL	NULL	BTREE
foo	1	B_2	3	D	A	4	NULL	NULL	BTREE

Note that if INDEX HINTS are used, then it does use the single column and no longer uses filesort, which seems to be better. I'd like MySQL to figure that out though.

explain extended
SELECT * FROM foo USE INDEX (B)
WHERE B = 1
ORDER BY A DESC;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	foo	ref	B	B	1	const	2	100	Using where

Indexing for 'Order BY' with the combination of 'WHERE' works differently. Please go through the below link for more details dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html — Santosh D.
– Santosh D., Commented Mar 17, 2022 at 6:29

Rick James · Accepted Answer · 2022-03-17 06:29:07Z

0

Change INDEX(B) to INDEX(B,A)
Use Engine=InnoDB, not MyISAM.
Please provide SHOW CREATE TABLE foo, there may be other subtle issues.
Please provide EXPLAIN FORMAT=JSON SELECT ... for more details

answered Mar 17, 2022 at 6:29

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kaleb Loo Over a year ago

I believe MySQL uses InnoDB by default which also appends the PRIMARY INDEX to each SECONDARY INDEX by default.

Rick James Over a year ago

@KalebLoo - What version of MySQL are you using? (The SHOW will confirm whether the table is InnoDB.)

Kaleb Loo Over a year ago

5.6. I've tried on 5.7 and 8.0 and saw the same thing. I confirmed that it is using InnoDB

Rick James Over a year ago

@KalebLoo - Hmmm... Maybe the SHOW and JSON would give me some other clue.

O. Jones · Accepted Answer · 2022-03-17 11:41:28Z

Presumably this is an example problem. Because with a TINYINT AUTO_INCREMENT column you'll not ever have more than 127 rows in this table. Indexes don't matter much, if at all, for tables that size. Most real-world tables use BIGINT for their auto incrementing columns these days.

Ttrying to understand execution plans for tables that tiny is a waste of time: the query planner chooses indexes partly based cardinality statistics, and tiny tables don't have much of those. You'll learn more about index usage with a realistically-sized table.

Also, filesort doesn't necessarily mean "I, MySQL, am so dumb I had to write that dataset to a file system file to sort it". It means "I had to create a table-like data structure to do the sort". filesort isn't always as expensive as it sounds.

All that being said, for example query an index on (B, A DESC, C, D) will cover your example query. It enables a query plan to random-access to the first eligible row (the first row where B = 1), then scan the index sequentially to get the rows in the order you requested. Then it can fetch the data from the index without going back to the table. Covering indexes can be very fast at satisfying particular queries, at the cost of storage space.

In realistically-sized tables, your index on just (B) is made unnecessary by your index on (B, C, D). If you have very large tables (measured in hundreds of megarows) you may find some small I/O savings for some queries that use just the (B) index. Otherwise not.

And, try using EXPLAIN ANALYZE (just ANALYZE on some MySQL / MariaDB versions). It actually runs the query and shows you the actual execution plan.

With real data and real queries, with the composite index it actually starts doing index joins as opposed to doing ref joins with the single index when I specify using USE INDEX

Collectives™ on Stack Overflow

MySQL seems to ignore single column index for composite index when sorting by primary key

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related