Link to home
Start Free TrialLog in
Avatar of dportabella
dportabella

asked on

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;

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

>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
Avatar of dportabella
dportabella

ASKER

angellll,
how to do that?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial