Long query execution in MySQL

Question

MySQL

Good afternoon.
Unexpectedly for me, one of the projects began to develop very quickly and now there was a problem with the database.
PHP + MySQL bundle is used.

There is a table, it already has ~ 20 million records%)
Table structure:

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| task_id | int(9)        | NO   | PRI | NULL    |       |
| user_id | int(9)        | NO   | PRI | NULL    |       |
| checked | enum('0','1') | NO   | MUL | 0       |       |
| taken   | enum('0','1') | NO   | MUL | 0       |       |
+---------+---------------+------+-----+---------+-------+

Request:

SELECT DISTINCT `task_id` FROM `tasks_pending` WHERE `checked`='0' AND `taken`='0' LIMIT 50;

The problem is that the query execution time is very large, ~3 minutes , which is unacceptable.

I read a lot about indexes, arranged everything I could:


The first index (task_id) cannot be removed, it is the basis.
The machine is quite powerful and I think it should pull faster: Intel® Core™ i7-2600, RAM: 16 GB, the whole table should fit in its entirety.

The machine was configured with the mysqltuner script, I always used it and had no problems until I ran into High-Load'om.

EXPLAIN says the following: screenshot, because it doesn't fit Can
you please tell me what I did wrong and how to fix it? Thank you.

UPD:Many thanks to everyone for the advice, every advice is good, so I can’t choose a better answer, I thanked everyone for their help in the profile.

Answer 1

Try the compound key checked, taken, task_id. In that order, it might help. What is an InnoDB table anyway?

Answer 2

You are using DISTINCT , which is bad in itself.
"If LIMIT # is specified with DISTINCT, MySQL will stop as soon as it finds # unique rows."
I advise you to read about how MySQL optimizes DISTINCT:

• happy.kiev.ua/mysql/manual.ru_MySQL_Optimization.html#DISTINCT_optimization
  
• habrahabr.ru/post/125428/
  

Answer 3

task_id_2 is redundant, the query that will use it in the same way will be able to use the first half of the primary index. so task_id_2 is a duplicate and you need to remove
the indexes separately for checked and separately for taken are not very useful.
index by (checked, taken, task_id) that the first commenter advised you - it really makes sense to try.

Answer 4

1. Show MySQL config,
2. One mysqltuner is not enough for you
3. I advise you to replace MySQL with Perocona Server.

Answer 5

Try to hang a composite index on checked,taken or even on task_id, checked,taken
and you can try changing the query to a query like
select task_id from tasks_pending where checked=0 and taken=0 group by task_id limit 50;

Answer 6

Replace ENUM with INT(1) UNSIGNED, forget about ENUM.

Answer 7

Materialize the query result. Create a separate table, insert when tasks arrive, delete when assigned.