индексируйте справку для запроса MySQL, использующего больше - чем оператор и ORDER BY

Старайтесь избегать использования диапазонов (например,>,> =, <, <=) в качестве крайней левой части предложения WHERE. MySQL не может использовать индекс для каких-либо полей в предложении WHERE справа от диапазона.

На первый взгляд я бы сказал, по крайней мере, создать индекс для first_user_id, stop, second_user_id. Затем укажите запрос соответственно:

выберите * from tbl_name, где first_user_id = N и остановите> = M и second_user_id = N

ОБНОВЛЕНИЕ: Да, так что я полностью противоречил себе в приведенном выше запросе - так как включил second_user_id в index бесполезен при указании его в WHERE после "диапазона" остановки, поэтому давайте попробуем это еще раз.

ALTER TABLE имя_таблицы ADD INDEX index_1 (first_user_id, stop) ALTER TABLE tbl_name ADD INDEX index_2 (first_user_id, second_user_id, stop)

Странно то, что ваш запрос возвращает только 238 строк (?)

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

Кроме того, не забудьте впоследствии удалить неиспользуемые индексы :)

edit

Это дикая догадка (потому что я не уверен, что mysql не преобразовывает запрос в его текущую форму), но вы можете попробуйте сделать следующее:

SELECT * FROM (
    SELECT * 
      FROM tbl_name 
     WHERE stop >= M 
       AND first_user_id=N 
    ) AS derived
ORDER BY start ASC

Не могли бы вы сделать так, чтобы ваша таблица-пример и результаты EXPLAIN совпадали? Потому что, очевидно, это не одна и та же ситуация, и мы не знаем, может быть, вы допустили ошибку, абстрагировав свой реальный запрос только на основе предоставленных результатов EXPLAIN. Если вы не хотите показывать слишком большую структуру, то сделайте наоборот, создайте структуру цитируемой таблицы и предоставьте результат EXPLAIN по ней (возможно, таким образом вы поймаете проблему).

Теперь ясно одно - для сортировки используется filesort, что плохо.

Для упрощения (к этому мы еще вернемся) - составные индексы, полезные для сортировки, должны иметь перед собой поле sort.

Пример idx(ID, Start)

ID      Start
1
        5
        8
        8
        10
        25
2
        3
        9
        10
        40
        41
        42
        42
...

В приведенном примере индекс не слишком полезен для сортировки, если у вас нет условия where, в котором ID ограничен только одним значением.

Но, это исключение важно, поскольку у вас есть возможность выбора одного ряда по одному или обоим полям id.

Итак, из ваших индексов только индексы, которые имеют начало в начале

start_index: [start, first_user_id, second_user_id]
test_3_index: [start,stop,first_user_id,second_user_id]

Mysql игнорирует индекс

start_index: [start, first_user_id, second_user_id]

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

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

Поэтому, чтобы помочь планировщику, вы можете создать индекс, который будет использовать селективность по одному значению с индексом типа:

two_ids_with_sort: [first_user_id, second_user_id, start]

Я предполагаю, что вышеописанное будет очень хорошо работать на вашем втором запросе, где у вас есть условия на обоих id, дающие вам доступ к предварительно отсортированным указателям начальных записей. Следующий запрос должен сделать то же самое для первого запроса:

one_id_with_sort: [first_user_id, start]

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

Здесь есть два пути a) добавить поле stop в конец индекса b) создание еще двух похожих индексов со стопом вместо старта (здесь можно использовать пересечение индексов, что может быть полезно для более широкого круга запросов)

Но проверьте все вышеперечисленные теории.

Пара общих рекомендаций

• сначала напишите условия наиболее избирательным образом
• при тестировании индексов начните с одноколоночных индексов, а затем переходите к составным индексам (например, для сортировки по старту я бы добавил индекс только по старту)
• слишком много индексов не очень хорошо в mysql, так как планировщик запросов не в состоянии не может быстро просмотреть все возможные комбинации и не может правильно оценить затраты на все операции (поэтому он срезает углы и лучшая комбинация индексов и план могут быть упущены)
• поэтому тестируйте индексы с помощью USE INDEX (index1) FOR ORDER BY в вашем select, чтобы оценить преимущество определенного индекса над планировщиком, см. подробнее здесь (особенно опция FORCE; также - стремитесь оставить только полезные индексы и посмотреть, сможет ли планировщик использовать их затем, если нет, то только в крайнем случае, принудительно используйте индексы в запросах, для которых производительность имеет решающее значение. Помните, что это плохая практика с точки зрения администрирования и дизайна).