MySQL How can I speed up this SELECT query?

Question

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.

Answer 1

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.