• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

efficiency in MySQL

I have a simple table as follows:
mysql> describe task;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| state                 | int(11)      | YES  | MUL | NULL    |                |
| fails                 | int(11)      | YES  | MUL | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

There are 100'000 rows in the table.
Note that 'state' and 'fails' columns are indexed.

The problem is that this simple query takes 0.40 seconds, which it is too much:
SELECT COUNT(*)  FROM Task WHERE fails < 3 AND state=1;


0.00 seconds: SELECT COUNT(*)  FROM Task;
0.04 seconds: SELECT COUNT(*)  FROM Task WHERE state=1;
0.00 seconds: SELECT COUNT(*)  FROM Task WHERE fails=1;

0.40 seconds: SELECT COUNT(*)  FROM Task WHERE fails < 3 AND state=1;
0.40 seconds: SELECT COUNT(*)  FROM Task FORCE INDEX(state) WHERE fails < 3 AND state=1;
0.70 seconds: SELECT COUNT(*)  FROM Task FORCE INDEX(fails) WHERE fails < 3 AND state=1;

Some info
+++++++++++++++++++++++++++++++
mysql> describe SELECT COUNT(*)  FROM Task WHERE state=1;
+----+-------------+-------+------+---------------+-------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows  | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | Task  | ref  | state         | state | 5       | const | 50903 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> describe SELECT COUNT(*)  FROM Task WHERE fails=1;
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | Task  | ref  | fails         | fails | 5       | const |   31 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(*)  FROM Task WHERE fails < 3 AND state=1;
+----+-------------+-------+------+---------------+-------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows  | Extra       |
+----+-------------+-------+------+---------------+-------+---------+-------+-------+-------------+
|  1 | SIMPLE      | Task  | ref  | fails,state   | state | 5       | const | 51871 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

++++++++
What can I do to speed up (10 times) the execution time for this query?
SELECT COUNT(*)  FROM Task WHERE fails < 3 AND state=1;

0
dportabella
Asked:
dportabella
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Note that 'state' and 'fails' columns are indexed.
you have 2 indexes, right?
try to add the fails column as second field to the index that has state column
0
 
dportabellaAuthor Commented:
angellll,
how to do that?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you drop the current index, and create a new one.
DROP INDEX current_index_name;
CREATE INDEX new_index_name ON Task ( state, fails )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now