Very slow query with ORDER BY and LIMIT

Question

I have a very slow query because using ORDER BY. Now i understand why it is slow but i have no idea how i make it faster.

The table got like 900.000 records. (the reason why it is slow)

SELECT SQL_NO_CACHE id, name 
FROM users where is_provider = 0 
AND activated_at IS NOT NULL
AND is_ngo = 0 
AND deleted_at is NULL 
AND is_cancelled = 0   
ORDER BY name 
LIMIT 60000, 90;

I use limit because i use pagination. SQL_NO_CACHE because i dont want to use cache for testing.

This query takes like 60 seconds, which is way too long. It is a background task so if i can reduse it in 5 seconds it will be ok

I have indexes on the activated_at column and deleted_at which are time fields. The other ones are booleans so no indexes needed.

Thanks

Answer 1

I think indexing is the answer but you have to figure out the correct index for your query so you should try this:

EXPLAIN SELECT SQL_NO_CACHE id, name 
FROM users where is_provider = 0 
AND activated_at IS NOT NULL
AND is_ngo = 0 
AND deleted_at is NULL 
AND is_cancelled = 0   
ORDER BY name 
LIMIT 60000, 90;

As detailed thoroughly in this very old, but useful article:
http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm