индексы и эффективность их использования ;)

[sad]

индексы и эффективность их использования

MyISAM. табличка 3 000 000+ записей.
в ней хранятся мессаги юзеров. поля: id - уникальное, автоинкремент, user_id - id юзера, mess - само сообщение. т.е. там примерно такое содержимое:

id | user_id | mess
----------------------------
1  | 1       | превед
2  | 2       | кагдила
3  | 2       | мессага
4  | 2       | мессага
5  | 1       | мессага
6  | 3       | мессага
7  | 2       | мессага
8  | 1       | мессага

всего 10 000 юзеров, т.е. в среднем на каждого юзера по 3 000 записей
есть индексы: primary(id), id(id), uid(user_id), id_uid(id,user_id).
спрашивается, какой индекс будет эффективнее всего использовать при запросе:
select * from mess where user_id=123 order by id

з.ы. вопрос задан не с целью узнать, какой же все таки, а с целью услышать мнение остальных, т.к. ответ я уже выяснил экспериментальным путем, и он идет врознь с моими предположениями и ожиданиями

 

[zerkms]

вероятно - сделать составной индекс uid_id(user_id, id) и использовать именно его

 

[sad]

zerkms, читай внимательнее.. есть такой индекс

но ответ ясен, я тоже так решил.
кроме того, делал explain:
с использованием индекса uid, в Extra: Using where; Using filesort;
с использованием индекса id_uid: Using where;

теперь теория: что быстрее?

 

[zerkms]

sad
_такого_ индекса нет - есть id_uid. под рукой mysql нет чтобы проверить разницу, но думаю она есть

 

[sad]

ну еще раз акцентирую внимание на цифрах приведеных выше...
с использованием индекса uid, в Extra: Using where; Using filesort; в rows: 3 000
с использованием индекса id_uid: Using where; в rows: 3 000 000

-~{}~ 03.04.06 16:11:

так, а в чем разница? судя по документации разницы в порядке нет

 

[zerkms]

ps: кроме Extra посмотри ещё что в possible keys и в key

-~{}~ 03.04.06 19:14:

sad
перечитай информацию по индексам

с использованием индекса uid, в Extra: Using where; Using filesort; в rows: 3 000

потому что выражение в where проиндексировано (индекс - user_id)

с использованием индекса id_uid: Using where; в rows: 3 000 000

потому что используемый в where user_id не первый в индексе. так что поменяй свой индекс id_uid на uid_id и посмотри ещё раз

 

[sad]

сам mysql по дефолту предлагает использовать uid, и только его
id_uid использовал через force index.
что в key я писал: uid и id_uid соответственно.

 

[zerkms]

sad
повторяю в третий раз индексы id_uid (id, user_id) и uid_id (user_id, id) НЕ РАВНОЗНАЧНЫ.
перечитай ВНИМАТЕЛЬНО мой предыдущий пост и не поленись сделать то что я там тебе насоветовал

 

[sad]

спасибо, перечитал, действительно в where может использоваться только "левая часть" составного индекса
действительно работает по-другому

 

[zerkms]

sad
вероятно что при использовании uid_id также будет заюзан id и при сортировке - но это догадка, нужно пробовать - вполне вероятно, что нет

-~{}~ 03.04.06 19:34:

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

запрос №2 из верхнего блока примеров