Форум пользователей MySQL

А можно попросить еще дамп из десятка-другого строчек?

Начнем с того, что построенный индекс не влияет на сортировку запроса, даже, выполненный в одну сторону:

 EXPLAIN SELECT *
FROM `topic`
FORCE INDEX ( gid )
ORDER BY `pinned` ASC , `last_post_time` ASC 

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     topic     ALL     NULL     NULL     NULL     NULL     5     Using filesort

Теперь пробуем так:

ALTER TABLE `topic` DROP INDEX `gid`;
ALTER TABLE `topic` ADD INDEX `for_sort` ( `pinned` , `last_post_time` ) ;
ALTER TABLE `topic` ADD INDEX `for_search` ( `gid` , `fid` , `st` ) ;
ANALYZE TABLE `topic`;

EXPLAIN SELECT *
FROM `topic` FORCE INDEX (for_sort)
ORDER BY `pinned` ASC, `last_post_time` ASC;

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     topic     index     NULL     for_sort     9     NULL     5      

EXPLAIN SELECT *
FROM `topic` FORCE INDEX (for_sort)
ORDER BY `pinned` ASC, `last_post_time` DESC;

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     topic     ALL     NULL     NULL     NULL     NULL     5     Using filesort

explain SELECT *
FROM `topic` FORCE INDEX (for_sort, for_search)
WHERE `gid` =1
AND `fid` =6
AND `st` != 'TRASH'
ORDER BY `pinned` ASC, `last_post_time` ASC

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     topic     ref     for_search     for_search     8     const,const     1     Using where; Using filesort

Из этих опытов делаем выводы:
1. Двунаправленной сортировке все равно нужен filesort.
2. Для сортировки без filesort помимо одного направления поиска нужен свой ключ
3. Оптимизатор MySQL выбирает из двух возможных индекс именно для поиска, предпочитая сортировать результат через filesort, даже однонаправленный.

В связи с этим вопрос: зачем Вы хотите оптимизировать запрос? Вам не нравится время его выполнения или слово filesort в результатах explain? Если второе - то оставьте, filesort при десятке найденных записей не страшен. Если первое, то напишу пару запросов - у них менее красивый explain, но на большом объеме данных могут выполняться быстрее, нужно проверять.

Ох как расписали Спасибо. Подробно!

Magz, время выполнения в 20 раз больше чем у других запросов, но и таблица пока не особо большая, потому я и обеспокоился. Неисправны индексы или логика приложения...

paulus, то есть в IN мне перечислить все нужные значения, кроме TRASH соответственно, я правильно понял? Попробую создать тогда вариант индекса, который будет объединять эти поля в одно... точнее просто заведу "служебное" поле для сортировки только один раз.

Да и я что-то не понял, но разве не надо для подобных запросов делать ОДИН индекс, который включает и поля входящие в WHERE и в ORDER BY? Разве нужно делать 2 индекса по этим частям запроса??? о.О

Спасибо за помощь, уже придумал решение, filesort ушел гулять

Может, немного реструктуризовать табличку? А то у Вас часть данных битовая  (pinned),
а часть — «в одном флаконе» (st). Логичнее сделать отдельные флажки «private»,
«erased» и т.п. Тогда не надо будет делать страшных зверей типа PRIVCLOSED.

А с индексом, к слову сказать, может быть сделать pinned «по смыслу» логическим совсем?
тогда 1 = прикреплена, 0 = не прикреплена, и (о, чудо ) сортировка нужна в ту же сторону,
что и дата.

Ну вот сел... реализовал частично что получил.

Теперь Лента форума выводится корректно (также как и раньше, и мы получаем желаемый результат) запросом:

Отредактированно Proger (15.04.2009 04:55:08)

Получилось сделать что-то вроде:

Данные данные мной реальные, разве что ники подменены ну и обрублены посты ибо длинные чота все попались просто по запросу ORDER BY RAND() из действующего проекта.

Лимит обычно 25, в максимальном значении 100.

Чтож будем пробывать, но не сегодня, запарился с JS и загрузкой проца на 100% =\ Спасибо.

дать больше может? мне не жалко просто попросили десяток, ну я выдал по быстрому только куда нить не в открытый уж источник

ммм... тупанул написав первый раз, вот что получил в результате:

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     t2     index     PRIMARY     last_post_time     8     NULL     95181     Using index
1     SIMPLE     t1     eq_ref     PRIMARY     PRIMARY     4     forum.t2.id     1     Using where

работает вроде быстро

Отредактированно Proger (16.04.2009 00:04:30)

Вот только проблемы с восстановлением возвращения нужного результата пока, но вроде потихоньку продвигаюсь... вообще запрос выполняется 0.004, после первого раза, потом уже 0.001, правда один раз скакнул на 0.1. но эт глюк наверное был

ЗЫ листать у меня запрещено, просто на одной странице выводятся последние темы + они же в виде рсс с тем же ограничением.

Спасибо большое, очень помогли, а то чуть голову не сломал думая что же делать и думал что совсем кривая логика приложения, писали по пьяне и т.п. )

Замутите!

«FORCE INDEX» Вам поможет Пример использования — написан Вами выше для таблички
t1, нужно то же сделать для t2.