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

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

CodePudding user response：

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

CodePudding user response：

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.