Mysql – Following thesql query is taking too much time for excecution

mysql-5.6

I have a MySQL table which is described in the following question.
Counting number of ids present in one table which is not presented on another for a particular user
The query which I am using is as follows.

SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
                   FROM   table2
                   WHERE  tableid = table1.id
                   AND    user_id = table1.user_id)

This query is taking too much time for considerably bigger table how can I improve the performance of this particular query.

Best Answer

table2 needs INDEX(tableid, user_id) (in either order).

If this does not solve the problem, please provide SHOW CREATE TABLE for both tables, plus EXPLAIN SELECT ....

Related Solutions

MySQL – Query ‘Optimizing’ State Taking Too Long

SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;

is well optimized if you have INDEX(updated_date) (which you do). It will do two probes. No need for two queries, etc.

The EXPLAIN said 'optimized away' because it could see that it did not need to do any work.

I checked with a similar table, then checked the Handler% STATUS values -- Handler_read_first and Handler_read_last incremented by 1 each. This is indicative of optimizing MIN and MAX.

Do not use OPTIMIZE TABLE it is (usually) a long wast of time.

It is a huge table.

DELETEing a million rows will queue up a lot of stuff, especially because of 10 secondary indexes. I'll bet that when you asked for MAX and MIN, it had to finish up all the pending index updates.

If that is the case, none of the suggestions will really solve the problem. Chunking the deletes (which is a good idea anyway) might have "hidden" the problem by slowing down the delete task. As @eroomydna says, the undo logs must have been huge.

On what basis are you DELETEing? If it is "purge all records older than X", then this is a cure: PARTITION BY RANGE(...) on the date (to use for purging) and DROP PARTITION to jetison old rows. This is also much cleaner (in the sense of OPTIMIZEing to defragment) than DELETEing.

Rolando, ANALYZE TABLE will recalc the stats "instantly". OPTIMIZE TABLE rebuilds (and ANALYZEs) the table but takes forever on this sized table.

Potentially valid is to copy over the useful rows, a la Rolando's #4. But only if you are jettisoning "most" of the table. I suggest that is not the case here -- "deleting a few million rows" out of 618M.

10 secondary keys is a lot. Would you care to show them; we may have suggestions on pruning the list. For this sized table, it is costly to maintain that many.

Edit -- "Change buffering"

I believe what I have described is called change buffering for DELETEs. More discussion: http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-performance-change_buffering.html http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-change_buffering.html

You could try innodb_change_buffering = none.

MySQL queries taking very long/not finishing

Few points here:

"Cartesian product" is not simple :)
In your specific query, and based on the index you have, the time will highly depend on the distribution of the data. Try this query: SELECT COUNT(*) FROM table WHERE column2=''; I guess the result is not a small number.
UNIQUE entry (column2, column3, column4, column5); is not healthy. The index is very big. It affects both, reads and writes. 'Reads' because it has to be loaded into the memory when it is used, and 'Writes' because it has to be checked (and maybe updated) whenever a DML operation is executed. If this uniqueness is a must, I suggest adding an MD5 hash for all the fields, and have that field uniquely indexed.
To get smaller result, avoiding repetition, you may add WHERE... AND a.column1<b.column1. This will avoid you having:
```
1, 4
4, 1
```

Best Answer

Related Solutions

MySQL – Query ‘Optimizing’ State Taking Too Long

MySQL queries taking very long/not finishing

Related Question