SQL gets slow on a simple query with ORDER BY

Question

I have problem with MySQL ORDER BY, it slows down query and I really don't know why, my query was a little more complex so I simplified it to a light query with no joins, but it stills works really slow.

Query:

SELECT
    W.`oid`
FROM
    `z_web_dok` AS W
WHERE
    W.`sent_eRacun` = 1 AND W.`status` IN(8, 9) AND W.`Drzava` = 'BiH'
ORDER BY W.`oid` ASC 
LIMIT 0, 10

The table has 946,566 rows, with memory taking 500 MB, those fields I selecting are all indexed as follow:

oid - INT PRIMARY KEY AUTOINCREMENT
status - INT INDEXED
sent_eRacun - TINYINT INDEXED
Drzava - VARCHAR(3) INDEXED

I am posting screenshoots of explain query first:

The next is the query executed to database:

And this is speed after I remove ORDER BY.

I have also tried sorting with DATETIME field which is also indexed, but I get same slow query as with ordering with primary key, this started from today, usually it was fast and light always. What can cause something like this?

Answer 1

The kind of query you use here calls for a composite covering index. This one should handle your query very well.

CREATE INDEX someName ON z_web_dok (Drzava, sent_eRacun, status, oid);

Why does this work? You're looking for equality matches on the first three columns, and sorting on the fourth column. The query planner will use this index to satisfy the entire query. It can random-access the index to find the first row matching your query, then scan through the index in order to get the rows it needs.

Pro tip: Indexes on single columns are generally harmful to performance unless they happen to match the requirements of particular queries in your application, or are used for primary or foreign keys. You generally choose your indexes to match your most active, or your slowest, queries. Edit You asked whether it's better to create specific indexes for each query in your application. The answer is yes.