MySQL How can I speed up this SELECT query?
mysql, optimization, select
Solution
You should add an index on `(tried, id)`.
You already added an index on `(username,id,tried)` but this index cannot be used efficiently for the query you wrote because the field `tried` is not the first item in the index.
From the manual page How MySQL Uses Indexes:
MySQL uses indexes for these operations:
To find the `MIN()` or `MAX()` value for a specific indexed column `key_col`. This is optimized by a preprocessor that checks whether you are using `WHERE key_part_N = constant` on all key parts that occur before `key_col` in the index. In this case, MySQL does a single key lookup for each `MIN()` or `MAX()` expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:
SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
Note in particular that the `WHERE` clause must refer to columns that occur before the column for which you wish to calculate the `MIN` or `MAX`, which is why your current index is not used efficiently.
I need to rapidly do select queries on it in a loop
Are you sure that you need to query in a loop? Have you considered moving the loop into the database? Maybe what you really need is a join? It's better to send a single query that fetches a lot of data than lots of small queries that fetch only a small amount of data each.
Problem
I have a very large table of over 9 million rows and in my software I need to rapidly do select queries on it in a loop. The problem is that the queries take almost 4 seconds to complete. Here is one of the queries (they are all similar in that they all have the same WHERE clause that is slowing them down: ``` SELECT MIN(id) FROM `04c1Tg0M` WHERE `tried` = 0; ``` I'm using the tried column as a boolean. the value is either 1 or 0. Here is the EXPLAIN from that query: ``` --------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+ | 1 | SIMPLE | 04c1Tg0M | index | NULL | pdex | 158 | NULL | 9275107 | Using where; Using index | +----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+ ``` Here is the table structure: ``` 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`) ) ENGINE=MyISAM AUTO_INCREMENT=9275108 DEFAULT CHARSET=utf8 ``` Here is the output of show indes: ``` | 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 | 9275107 | NULL | NULL | | BTREE | | | 04c1Tg0M | 1 | pdex | 1 | username | A | 9275107 | NULL | NULL | | BTREE | | | 04c1Tg0M | 1 | pdex | 2 | id | A | 9275107 | NULL | NULL | | BTREE | | | 04c1Tg0M | 1 | pdex | 3 | tried | A | 9275107 | NULL | NULL | YES | BTREE | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ``` And here is the output from the query in question: ``` +---------+ | MIN(id) | +---------+ | 1 | +---------+ 1 row in set (3.76 sec) ``` I need to drastically reduce the query time. Any help is greatly appreciated.