MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table.
JobTable (userid, status, ...)
   userid_status_index (userid, status)

When I run some queries:
   select count(*) from JobTable where userid=&quot;1&quot; and status=&quot;ready&quot;
   Result: 20

For explain:
   explain extended select count(*) from JobTable force index (userid_status_index) where userid=&quot;1&quot; and status=&quot;ready&quot;
   rows: 2500

When I do explain, the number of rows keeps increasing (as I keep inserting, but the deleted rows are probably not removed from the index), but when i do count(*) on the actual table, it shows lesser rows.
After few rows, the DiskIOPS increases and things are back to normal. I suppose this is due to some change buffer update. Can I force it to happen more periodically so that the queries are not slow?
Forcing the index also doesn't help.
Also note that the queries are actually slow when it's run, not just the explain is incorrect.
Mysql 5.6
Is this the real query?
select count(*) from JobTable where userid=&quot;1&quot; and status=&quot;ready&quot;

If so, here's how it works when you have INDEX(userid, status) or INDEX(status, userid)

It drills down the index's BTree to the first entry with that pair of values.
Count 1.
Scan forward until the pair no longer matches (else back to step 2).

The only thing that can change is the details of the BTree.  INSERTs and DELETEs may lead to block splits and block merges.
Don't trust the numbers in EXPLAIN.
In this case, there is no benefit in using FORCE/USE INDEX; the index to use is too obvious.  (Or do you have a case where it used some other index?  If so, let's see SHOW CREATE TABLE.)
While we are at it, neither ordering in the INDEX is better.  That is, the cardinality of each of the two columns does not matter.
ANALYZE TABLE JobTable will refresh the statistics; it is fast, but imprecise, for InnoDB.