Почему order by ускоряет запрос?

Question

[WebDev]

select `id`
from `posts`
where `posts`.`created_at` >= '2016-01-24'
and `is_duplicate` = 0
and `deleted` is null
and exists (select 1 from `entity_user` where `entity_user`.`user_id` = 2 and `entity_user`.`entity_id` = `posts`.`entity_id`) 
limit 20

Вот такой код выдает в explain 2 миллиона строк в поле rows, а если добавить сортировку, то 20

select `id`
from `posts`
where `posts`.`created_at` >= '2016-01-24'
and `is_duplicate` = 0
and `deleted` is null
and exists (select 1 from `entity_user` where `entity_user`.`user_id` = 2 and `entity_user`.`entity_id` = `posts`.`entity_id`) 
order by `posts`.`created_at` desc 
limit 20

Индекс используется вот такой created_at_post_type_is_duplicate_deleted.
Почему так происходит? Везде всегда пишут, что сортировка замедляет запрос, а тут получается ускоряет?

Answer 1

[xmoonlight]

Почему так происходит?
1. Потому, что выводится в результате меньше ровно на (2 000 000 - 20) записей.
Или, другими словами, диапазон: 1 999 980 ... 2 000 000
2. Обратная сортировка 'desc' - смотрит в диапазоне от >='2016-01-24' до последней (самой новой) даты и сортировку этого списка - реверсирует, чтобы начать перебор с конца, поэтому кол-во просматриваемых записей - тут НЕСОИЗМЕРИМО! меньше и из-за этого запрос работает быстрее.

Comments

[romy4]

Вы приколист. Order by + limit работает по результату выборки, а никак не выбирает ровно 2000000-20 записей. Работать он будет одинаково что на Order by asc limit 20, что на Order by desc limit 20. Но если результатов где-то больше, то база их просто дольше будет отдавать клиенту. Это не влияет на скорость запроса.

[romy4]

Так что вам минус за знание внутренней работы mysql.

[xmoonlight]

romy4:
1. "а никак не выбирает ровно 2000000-20 записей"- возможно Вы имели ввиду: "а никак не выбирает меньше ровно на 2000000-20 записей"?
Это совсем разные цифры)
2. А про скорость отдачи, советую посмотреть на запрос по-внимательнее!
Там есть условие, которое автоматически индексируется, если является полем timestamp. В итоге, при сортировке по этому полю, автоматически отбрасывается всё, что выше (старее) этой даты и эти записи не просматриваются.

"Так что вам минус за знание внутренней работы mysql."
Ок. Минус - принят.

[romy4]

xmoonlight: нет. "мухи отдельно, котлеты отдельно". Order by всегда выполняется после полного выполнения всех where условий (в том числе и join). Поэтому на скорость выборки он никак не может повлиять. Что asc, что desc всегда выполняют _полную_ сортировку результата на которую уже в самом конце накладывается limit. Поэтому вывод про диапазон в корне неверен.

[xmoonlight]

romy4: то, что отсортировано уже по ключам при добавлении в базу - не сортируется при запросе, если нет доп. отношений с другими данными.
а timestamp - всегда сортируется по ключам внутри БД на системном уровне базы и это не зависит от следующих факторов никак: использовалось это поле в запросе, были ли условия для этого поля или была ли сортировка asc или desc.

[romy4]

xmoonlight: да. согласен.

Answer 2

[romy4]

Не ускоряет запрос. В первом случае какие ключи используются, а во втором?

Comments

[WebDev]

Одни и те же ключи используются. Вообще все поля эксплейна одинаковы, кроме rows

[romy4]

WebDev: интересно. можете привести оба explain?