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

Здравствуйте.

Lsync написал:

mysql> explain select * from `messroom`  where `roomID` IN (3,7) order by `ID` desc limit 5;
+----+-------------+----------+-------+---------------+--------+---------+------+---------+-----------------------------+
| id | select_type | table    | type  | possible_keys | key    | key_len | ref  | rows    | Extra                       |
+----+-------------+----------+-------+---------------+--------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | messroom | range | roomID        | roomID | 5       | NULL | 5410580 | Using where; Using filesort |
+----+-------------+----------+-------+---------------+--------+---------+------+---------+-----------------------------+

Покажите show create table messroom; пожалуйста.

Lsync написал:

Почитал про принципы работы индексов, попробовал добавить индекс roomID+id - никаких изменений, кроме того, что explain показывает, что планирует использовать именно его.

Ну вообще на этот запрос идеально подходит именно индекс (roomID, id);
Покажите explain с ним. И как насчет скорости выполнения - возрастает?
Хотя mysql иногда ошибается в анализе - попробуйте выполнить по отдельности два запроса
select * from `messroom`  where `roomID`=3 order by `ID` desc limit 5;
select * from `messroom`  where `roomID`=7 order by `ID` desc limit 5;
И покажите explain'ы на них тоже.

Lsync написал:

Если нужно - могу привести все данные о таблицах и времени выборок, но что-то мне подсказывает, что существует какое-то типовое решение для подобных случаев. Поиск по форумам пока не помог - не нашел примеров, которые либо адаптировались бы для моих условий, либо которые я хоть как-то способен понять.

Просьба поделиться ссылкой на пример, который подошел бы под мои условия, и/или хотя бы вкратце разъяснить, какова концепция решения.

Табличку messroom сможете если что выложить на какой-нибудь файлообменник? Чем больше там записей будет тем лучше. Пока не надо - но если что попрошу.

Касательно концепции решения - она крутится вокруг составного индекса на (roomID, id)
Есть вероятность, что запрос будет иметь смысл переписать в виде

select * from `messroom`  where `roomID`=3 order by `ID` desc limit 5
UNION
select * from `messroom`  where `roomID`=7 order by `ID` desc limit 5;
 

vasya написал:

Насколько я понимаю, через union придется переписывать, потому что иначе будет использована только первая часть индекса для условия `roomID` IN (3,7) по типу range и далее файлсорт.

То есть MySQL сам не сообразит, что условие IN(3,7) можно хорошо соптимизировать, да? Это ж ведь не range на самом деле.
Есть еще sort_union, я на него натыкался  (подробности здесь: http://sqlinfo.ru/forum/viewtopic.php?id=3884), правда там условие OR состояло из двух полей различных, а не на два значения различных на одно поле.

Ну если не более N строк каждого типа, тогда union. Ну или просто двумя запросами - ничем не хуже по большому счёту. C составным индексом, указанным выше должно хорошо получиться. Подождём пока отпишет топикстартер .

Lsync написал:

(плюс где-то прочитал, что на индексы таблиц InnoDB MySQL автоматически добавляет последним столбцом первичный ключ).

Это не так. Скорее вы читали, что вторичные ключи в innodb содержат указатель на первичный ключ, или вы читали явную дезинформацию.

Lsync написал:

mysql> explain select * from `messroom`  where `roomID`=3 order by `ID` desc limit 5;
+----+-------------+----------+------+------------------+--------+---------+-------+-------+-------------+
| id | select_type | table    | type | possible_keys    | key    | key_len | ref   | rows  | Extra       |
+----+-------------+----------+------+------------------+--------+---------+-------+-------+-------------+
|  1 | SIMPLE      | messroom | ref  | roomID,roomID_id | roomID | 5       | const | 84812 | Using where |
+----+-------------+----------+------+------------------+--------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from `messroom`  where `roomID`=7 order by `ID` desc limit 5;
+----+-------------+----------+------+------------------+-----------+---------+-------+--------+-------------+
| id | select_type | table    | type | possible_keys    | key       | key_len | ref   | rows   | Extra       |
+----+-------------+----------+------+------------------+-----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | messroom | ref  | roomID,roomID_id | roomID_id | 5       | const | 400652 | Using where |
+----+-------------+----------+------+------------------+-----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

А кто может объяснить такое странное поведение explain. С одной стороны из key_len мы видим, что используется только первая часть индекса, с другой в extra нет using filesort (который там появится, если мы будем сортировать по другому полю)

Lsync написал:

Пока не знаю, как это реализовать, но за совет спасибо.

select `нужные вам поля` from `messroom` m join (ваш текущий запрос с union-ами, где вместо * указано одно поле id) t on m.id=t.id;
(Да и вообще * никогда писать не стоит).