MySQL How can I speed up this SELECT query?

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.

Answers


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.


Adding an index on the tried column should speed up your queries. 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;


If you add an index on the tried column, your query should speed up.

You can use a command like

create index tried_ix1 on 04c1Tg0M (tried);

As a side note, if you are applying an index on an InnoDb table, please also consider whether or not this index should be clustered. For example, if you are going to access a lot of the tables record's, in a particular, perhaps sorted, order, for perhaps a report, then consider creating a clustered index based on the sorting requirements of that report.


Need Your Help

Use service to get GPS location, android?

android service gps location

This is first time I am using service and it sure looks a lot complicated from activity.

Automatically tracking development time

language-agnostic development-environment

I'm working on a personal project and I'd love to be able to say at the end:"I've spend X hours on this project". Now one way to solve this, is to use a manual time tracker (worked from: to:). I've...