StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Inconsistencies in index usage on the same query
text
Body
copied!<p>I have a table of over 9 million rows. I have a SELECT query that I'm using an index for. Here is the query:</p> <pre><code>SELECT `username`,`id` FROM `04c1Tg0M` WHERE `id` > 9259466 AND `tried` = 0 LIMIT 1; </code></pre> <p>That query executes very fast (0.00 sec). Here is the explain for that query:</p> <pre><code>+----+-------------+----------+-------+-----------------+---------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+-----------------+---------+---------+------+-------+-------------+ | 1 | SIMPLE | 04c1Tg0M | range | PRIMARY,triedex | PRIMARY | 4 | NULL | 10822 | Using where | +----+-------------+----------+-------+-----------------+---------+---------+------+-------+-------------+ </code></pre> <p>Now here is the same query except that I'm going to change the id to 6259466:</p> <pre><code>SELECT `username`,`id` FROM `04c1Tg0M` WHERE `id` > 5986551 AND `tried` = 0 LIMIT 1; </code></pre> <p>That query took 4.78 seconds to complete. This is the problem. Here is the explain for that query:</p> <pre><code>+----+-------------+----------+------+-----------------+---------+---------+-------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+-----------------+---------+---------+-------+---------+-------------+ | 1 | SIMPLE | 04c1Tg0M | ref | PRIMARY,triedex | triedex | 2 | const | 9275107 | Using where | +----+-------------+----------+------+-----------------+---------+---------+-------+---------+-------------+ </code></pre> <p>What is happening here and how can I fix it? Here are my indexes:</p> <pre><code>+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | 04c1Tg0M | 0 | PRIMARY | 1 | id | A | 9275093 | NULL | NULL | | BTREE | | | 04c1Tg0M | 1 | pdex | 1 | username | A | 9275093 | NULL | NULL | | BTREE | | | 04c1Tg0M | 1 | pdex | 2 | id | A | 9275093 | NULL | NULL | | BTREE | | | 04c1Tg0M | 1 | pdex | 3 | tried | A | 9275093 | NULL | NULL | YES | BTREE | | | 04c1Tg0M | 1 | triedex | 1 | tried | A | 0 | NULL | NULL | YES | BTREE | | | 04c1Tg0M | 1 | triedex | 2 | id | A | 9275093 | NULL | NULL | | BTREE | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> <p>And here is my table structure:</p> <pre><code>| 04c1Tg0M | CREATE TABLE `04c1Tg0M` ( `id` int(20) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `tried` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), KEY `pdex` (`username`,`id`,`tried`), KEY `triedex` (`tried`,`id`) ) ENGINE=MyISAM AUTO_INCREMENT=9275108 DEFAULT CHARSET=utf8 | </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload