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?

O. Jones · Accepted Answer · 2021-02-17T13:15:39.670

2

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.

edited Feb 17 '21 at 13:15

answered Feb 17 '21 at 11:48

O. Jones

103,626
17
118
172

Thanks for the tip, i am going to try it this way. But with this thinking, it's better to create for every different search different group of indexes instead of single ones on same table? – Ultrazz008 Feb 17 '21 at 12:04
I've created CREATE INDEX eracunindex ON z_web_dok (`Drzava`, `sent_eRacun`, `status`, `dat_zavrsena`) and query run in 0.0007 seconds, thank you, i was thinking about re-indexing my whole table.. – Ultrazz008 Feb 17 '21 at 12:05
1

**Boom! 3.3 seconds to 700 microseconds.** Please see my edit. And please take a look at https://use-the-index-luke.com/ to learn about effective indexing. – O. Jones Feb 17 '21 at 13:17
Adding to the final "Yes": Once you have designed the best index for each SELECT, get rid of dups and overlapping indexes. Specifically: When you have `INDEX(a,b)`, you don't need `INDEX(a)`. But `INDEX(b)` cannot be eliminated by it. – Rick James Feb 17 '21 at 19:16
Among the [MySQL Utilities](https://downloads.mysql.com/archives/utilities/) is a nice little command-line utility called [mysqlindexcheck](https://www.systutorials.com/docs/linux/man/1-mysqlindexcheck/). It will scan your database's tables and suggest indexes to drop because they're redundant with others. – O. Jones Feb 17 '21 at 22:21
1

Be cautious -- If you have a monthly task that is the only user of a query, it may suggest that it be dropped. – Rick James Feb 23 '21 at 16:23

score 1 · Answer 2 · answered Feb 17 '21 at 19:13

There may be an even faster way. (Or it may not be any faster.)

The IN(8, 9) gets in the way of easily handling the WHERE..ORDER BY..LIMIT completely efficiently. The possible solution is to treat that as OR, then convert to UNION and do some tricks with the LIMIT, especially if you might also be using OFFSET.

( SELECT ... WHERE .. = 8 AND ... ORDER BY oid LIMIT 10 )
UNION ALL
( SELECT ... WHERE .. = 9 AND ... ORDER BY oid LIMIT 10 )
ORDER BY oid LIMIT 10

This will allow the covering index described by OJones to be fully used in each of the subqueries. Furthermore, each will provide up to 10 rows without any temp table or filesort. Then the outer part will sort up to 20 rows and deliver the 'correct' 10.

For OFFSET, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or

SQL gets slow on a simple query with ORDER BY

2 Answers2