CODEWITHSUNDEEP
mysqlindexing
Kirill
Kirill

Reputation: 55

mysql doesn't use index. help me figure out why

there is the table test :

show create table test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `body` longtext NOT NULL,
  `timestamp` int(11) NOT NULL,
  `handle_after` datetime NOT NULL,
  `status` varchar(100) NOT NULL,
  `queue_id` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idxTimestampStatus` (`timestamp`,`status`),
  KEY `idxTimestampStatus2` (`status`,`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=80000 DEFAULT CHARSET=utf8

there is two select's

1) select * from test where status = 'in_queue' and timestamp > 1625721850;
2) select id from test where status = 'in_queue' and timestamp > 1625721850;

in the first select explain show me that no indexes are used in the second select index idxTimestampStatus is used.

MariaDB [db]> explain select * from test where status = 'in_queue' and timestamp > 1625721850;
+------+-------------+-------+------+----------------------------------------+------+---------+------+----------+-------------+
| id   | select_type | table | type | possible_keys                          | key  | key_len | ref  | rows     | Extra       |
+------+-------------+-------+------+----------------------------------------+------+---------+------+----------+-------------+
|    1 | SIMPLE      | test  | ALL  | idxTimestampStatus,idxTimestampStatus2 | NULL | NULL    | NULL | 80000    | Using where |
+------+-------------+-------+------+----------------------------------------+------+---------+------+----------+-------------+


MariaDB [db]> explain select id from test where status = 'in_queue' and timestamp > 1625721850;
+------+-------------+-------+------+----------------------------------------+---------------------+---------+-------+------+--------------------------+
| id   | select_type | table | type | possible_keys                          | key                 | key_len | ref   | rows | Extra                    |
+------+-------------+-------+------+----------------------------------------+---------------------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | test  | ref  | idxTimestampStatus,idxTimestampStatus2 | idxTimestampStatus2 | 302     | const |    4 | Using where; Using index |
+------+-------------+-------+------+----------------------------------------+---------------------+---------+-------+------+--------------------------+

Help me figure out what i'm doing wrong ? How should i create index for first select? why does the number of columns affect the index usage ?

Upvotes: 0

Views: 1334

Answers (1)

Rick James
Rick James

Reputation: 142518

What you saw is to be expected. (The "number of columns" did not cause what you saw.) Read all the points below; various combinations of them should address all the issues raised in both the Question and Comments.

Deciding between index and table scan:

  • The Optimizer uses statistics to decide between using an index and doing a full table scan.
  • If less than (about) 20% of the rows need to be fetched, the index will be used. This involves bouncing back and forth between the index's BTree and the data's BTree.
  • If more of the table is needed, then it is deemed more efficient to simply scan the table, ignoring any rows that don't match the WHERE.
  • The "20%" is not a hard-and-fast number.

SELECT id ... status ... timestamp;

  • In InnoDB, a secondary index implicitly includes the columns of the PRIMARY KEY.
  • If all the columns mentioned in the query are in an index, then that index is "covering". This means that all the work can be done in the index's BTree without touching the data's BTree.
  • Using index == "covering". (That is, EXPLAIN gives this clue.)
  • "Covering" overrides the "20%" discussion.

SELECT * ... status ... timestamp;

  • SELECT * needs to fetch all columns, so "covering" does not apply and the "20%" becomes relevant.
  • If 1625721850 were a larger number, the EXPLAIN would switch from ALL to Index.

idxTimestampStatus2 (status,timestamp)

  • The order of the clauses in WHERE does not matter.
  • The order of the columns in a "composite" index is important. ("Composite" == multi-column)
  • Put the = column(s) first, then one "range" (eg >) column.

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Upvotes: 3

Related Questions