1

We have an issue with MySQL InnoDB performance.
We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts processing them, it changes this status field to 1, then in case of an exception, the status goes to 2 or in case of successful processing, the status is set to 3.
The records remain in this table for three days then they are deleted. This way, the record count grows to around 1.7M and remains there.

If I run this:

describe SELECT * FROM temptable where status=0

It shows around 900k examined records, and the query itself returns 0 matching rows in about 5 seconds. There is an index on the status field. If I drop this index and recreate it, then the examined rows value goes down to 1 and starts to grow again, as new records are inserted.

If I query records with the STATUS value of 2 (Exception state), and none of these records existed ever, then the examined row count stays at 1 and the query returns instantly.

SELECT * FROM temptable where status=0
0 row(s) returned   5.538 sec / 0.000 sec
SELECT * FROM temptable where status=2
0 row(s) returned   0.000 sec / 0.000 sec

You can reproduce the same behaviour by creating a table like this:

CREATE TABLE temptable (
    someprimary int(11) NOT NULL AUTO_INCREMENT,
    status int(11) NOT NULL,
    PRIMARY KEY (someprimary),
    KEY IND_STATUS (status)
) ENGINE=InnoDB;

Fill it with around 100000 zero values, then update some zeros to ones:

update temptable set STATUS=1 where mod(someprimary,11)=0  

Set the remaining zeros to 3:

update temptable set STATUS=3 where STATUS=0

Now there are some STATUS=1 records, many STATUS=3 records and none with the value 0 or 2. You can now run the aforementioned selects and see for yourself if the query where we search for 2 returns instantly and the search for 0 requires some noticeable time to complete.
Is there any way to solve this issue and get the results instantly if there are only between 0-10 records matching the query and there is an index on this field?

Update:

SELECT * FROM temptable where status=0;
Empty set (1.56 sec)

describe SELECT * FROM temptable where status=0;  
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+  
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows   | Extra       |  
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+  
|  1 | SIMPLE      | temptable | ref  | IND_STATUS    | IND_STATUS | 4       | const | 144651 | Using index |  
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+  
1 row in set (0.00 sec)

SELECT * FROM temptable where status=2;  
Empty set (0.00 sec)  
describe SELECT * FROM temptable where status=2;  
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+  
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | Extra       |  
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+  
|  1 | SIMPLE      | temptable | ref  | IND_STATUS    | IND_STATUS | 4       | const |    1 | Using index |  
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+  
1 row in set (0.00 sec)
(total record count is now 289303)  
SELECT count(1) db, STATUS FROM temptable group by STATUS;
+--------+--------+
| db     | STATUS |
+--------+--------+
|  25808 |      1 |
| 262864 |      3 |
+--------+--------+
2 rows in set (1.55 sec)

Thank you!

  • Try analyze table temptable; instead of recreating index to see if maybe your stats updates are misconfigured? – jkavalik Feb 29 '16 at 8:46
  • Check bugs.mysql.com/bug.php?id=79271 too, if it may be relevant. And try comparing EXPLAIN/DESCRIBE with different index hints. Can you add the results of describe SELECT * FROM temptable where status=0? Is it using the index? – jkavalik Feb 29 '16 at 8:48
  • Analyze table reports everything OK: analyze table temptable -> ; +-------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+---------+----------+----------+ | NetWork.temptable | analyze | status | OK | +-------------------+---------+----------+----------+ 1 row in set (0.01 sec) I updated the post with the "describe" results. I found out that, since I left the sample table untouched for about an hour, the stats have been updated. I had to insert another ~100000 records to reproduce the error. – zseeeeee Feb 29 '16 at 10:14
  • show variables like 'innodb_stats_on_metadata'; – jkavalik Feb 29 '16 at 10:57
  • Did the analyze change the plan for status=0? (describe before and after analyze). – jkavalik Feb 29 '16 at 10:58
1

DESCRIBE, alias EXPLAIN, estimates the number of rows. In your case (frequent changing of status and deletion), the number are very likely to be very far off. Do not trust it.

EXPLAIN gets its information from "statistics" about the distribution of the data in the table. The statistics are updated at certain times. Version 5.7 does the update at quite different times than 5.5. (What version are you running?)

You have INDEX(status)? Perhaps you should not; it is adding to the problem and not helping the solution in a consistent way. How often are the status changes? The DELETEs? There may be workarounds.

The Optimizer will look at the statistics for status and incorrectly decided that status=0 was too common for using the INDEX, so it did a table scan instead. On the other hand, the stats said that status=2 was rare, so it did use the index and 'instantly' discovered zero rows.

Alas, running an EXPLAIN, even immediately after running the equivalent SELECT does not necessarily give you the explain plan for that select. (I suspect this explains the mysterious results you got. I have almost never seen such happen.)

Your test case is not quite correct -- You have only 2 columns, the secondary key and the PRIMARY KEY, so you got Using index; This is unlikely to happen in the 'real' table -- don't you have other fields?

Please try one more thing to help get to the root of this. Rerun the tests with

FLUSH STATUS;
SELECT ... ;    -- (returning emptyset)
SHOW SESSION STATUS LIKE 'Handler%';

This will show whether it actually did an index (or table) scan, and was not just a caching problem, as hinted by @jkavalik .

  • Thank you for your reply. My queries really were running slow, not only the `EXPLAIN' was telling some odd numbers. How often are status changes? It depends, what you mean by often. Every record is inserted with status=0, then it gets processed almost instantly, so there is already a status change around 30ms after insertion, then depending on the result it gets into an 'exception' state or a 'processed state', so that's two status change for every record inserted, but then it is over, that record remains in this state for 72hours . – zseeeeee Mar 5 '16 at 10:19
  • Then, in every hour once runs a command, which deletes records that are more than 72 hours old. If the Optimizer thinks status=0 is too common, why doesn't it know that every status=0 gets updated into status=1 or status=2 or status=3 records, so they will be the common values, not status=0 anymore? – zseeeeee Mar 5 '16 at 10:24
  • I did the FLUSH STATUS. Is it possible, that it changed some behaviour of the whole MySQL server, not only for my current connection? Now everything looks fine (though it's not under full load, it's weekend). It looks like index stats gets updated around every ten seconds. Explain shows the examined row count rising from arund 20-to the 300-500 range in this 10 second period, then it falls back to ~20. We are running version 5.5.31-0 by the way. – zseeeeee Mar 5 '16 at 10:30
  • Try this: Keep the INDEX(status), and add FORCE INDEX(status) on queries where it is appropriate. – Rick James Mar 5 '16 at 18:06
  • Tried that before, didn't help – zseeeeee Mar 7 '16 at 9:06

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.