Very slow query with ORDER BY and LIMIT

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 activate开发者_运维百科d_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

---

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

---

You may find this article helpful: http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

It deals with the combination of ORDER BY and LIMIT.

---

I would guess that it is the ordering by name that makes it slow.

To test, remove the ORDER clause and check how long it takes.

The name field probably should be indexed when you want to order by it.

---

I'd suggest adding an index on the most selective boolean field, i.e., if only 5% of the rows in the DB have is_provider = 0, then you can significantly cut down the number of rows you need to scan for the other properties. If the distribution is 50/50 there's not much point, but I'd look at the distributions and let that guide you in determining which indexes may be helpful. Of course, you should be guided by actual performance (including those on other queries you may have).

---

Is the name column indexed as a part of another index?

Ordering by an index should be relatively quick (no need for filesort), unless name happens to be a part of an index, and not the first part. See more here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Please post your EXPLAIN results so we can help further...

---

You can try

SELECT  * FROM (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) t
LIMIT 60000, 90;

to avoid ORDER BY and LIMIT together.

---

The problem here is, Mysql is First trying to Order By all the Rows available in the table by Name and Then trying the Select the records basis the Where condition. So what you can try is First Select the records basis the Where Condition and run Order By name on result set.

Example:

SELECT  * FROM (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) t ORDER BY name
LIMIT 60000, 90;

---

ALternatively, remove the Order By clause and perform your ordering once you've read the records. It puts the work on the client, but that may be more reliable and faster if you database is slow (as it seems here).

Cheers,

Daniel