Очень медленный запрос с ORDER BY и LIMIT

Я думаю, что индексация - это ответ, но вам нужно определить правильный индекс для вашего запроса, поэтому вы должны попробовать это:

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;

Как подробно описано в этой очень старой, но полезной статье:
https://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

Эта статья может оказаться полезной: https://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

Он имеет дело с комбинацией ORDER BY и LIMIT.

Я предполагаю, что именно упорядочение по имени делает его медленным.

Для проверки удалите предложение ORDER и проверьте, сколько времени это займет.

Поле имени, вероятно, должно быть проиндексировано, если вы хотите упорядочить его.

Я бы предложил добавить индекс для наиболее селективного логического поля, т. е. если только 5% строк в БД имеют is_provider = 0, то вы можете значительно сократить количество строк, которые вам нужно сканировать для других свойств. Если распределение 50/50, в этом нет особого смысла, но я бы посмотрел на распределения и позволил вам определить, какие индексы могут быть полезны. Конечно, вы должны ориентироваться на фактическую производительность (в том числе и на другие запросы, которые могут у вас возникнуть).

В качестве альтернативы удалите предложение Order By и выполните упорядочение после прочтения записей. Это возлагает работу на клиента, но это может быть надежнее и быстрее, если ваша база данных медленная (как здесь кажется).

Ваше здоровье,

Даниэль

Индексируется ли столбец name как часть другого индекса?

Упорядочение по индексу должно быть относительно быстрым (нет необходимости в файловой сортировке), если только имя не является частью индекса, а не первой частью. Подробнее см. здесь: https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Пожалуйста, опубликуйте свои EXPLAIN результаты, чтобы мы могли помочь в дальнейшем...

Ты можешь попробовать

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;

чтобы избежать ORDER BY и LIMIT вместе.

Проблема здесь в том, что Mysql сначала пытается Order By все строки, доступные в таблице, с помощью Name, а затем пытается выбрать запись на основе условия Where. Итак, что вы можете попробовать, это сначала Select записи на основе Where условия и запустить Order By name на наборе результатов.

Пример:

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;