Mysql, оптимизация таблицы (создание индексов)

[ivank]

Есть таблица с (условно четырьмя полями): data (тип неважен), visible,
show_in_main, pub_date (DATETIME)

Требуется наиболее эффективно выполнять выборки типа (опять же неточно)
SELECT data, pub_date FROM tbl WHERE visible AND show_in_main AND
pub_date >= somedate AND pub_date < someother_date

Вопросы:
1. visible и show_in_main лучше сделать какого типа? Пока TINYINT, но
может ENUM или какой другой инт будет эффективнее?
2. По каким колонкам создавать индекс? Вижу три варианта:
a. (visible, show_in_main) и pub_date
б. (visible, show_in_main, pub_date)
в. (pub_date, visible, show_in_main)

http://dev.mysql.com/doc/refman/4.1/en/order-by-optimization.html я понял не до конца.

Стоит отметить, что у абсолютного большинства строк в таблице visible=show_in_main=1. Так что, наверное, простой индекс по pub_date меня устроит. Но, всё же, хочется чуть лучшего решения.

[vadimiron]

ivank
Я придерживаюсь политики создавать ключ по нейтральному от данных полю. То есть создаю столбик ID с auto_increment.
Единственный случай, когда я создаю ключ из самих данных, так это таблицы связи, чтобы избежать одинаковых записей.

Конечно, с такой политикой можно спорить, но у неё есть пара плюсов, которые меня убеждают:
Данные могут менятся: как тип так и название столбиков
Надо заботится, чтобы ключи были разные
При двухсторонней репликации баз данных без независимого от контекста данных ключа невозможно

И оптимизация: ничего быстрого, чем выборка по INT нет, или лучше сказать, выборка достаточно быстрая

И архитектура получается чистая и гибкая, у каждого объекта есть ID, и со временем могут поменятся хоть все столбцы, но сам объеект останется со всеми связями

[Vlad Drakula]

ivank
я бы наверное выбрал пункт 3 т.к. в этом случае объем ключей будет наверное минимален.
а вообще на скорость подобной выборки будет очень влиять настройки базы данных.

[Prisoner]

ivank, имхо, самое подлое в этой ситуации, что посоветовать что-то конкретное сложно - в современных СУБД очень много оптимизаций которые взаимозаменяют друг друга в зависимости от того, что есть на руках. К примеру если таблица для выборки очень мала (порядка 50 строк), то всегда будет использоваться медленное и обычно устраняемое связывание ALL, т.е. происходит прямое сканирование всей таблицы. В этом случае это быстрее чем все оптимизационные алгоритмы т.к. таблица умещается в памяти целиком. Вообще, лучше, имхо, почитать это (http://dev.mysql.com/doc/refman/5.0/en/explain.html) и сделать самостоятельные выводы. А еще это (http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html).

[ivank]

vadimiron
Первичный ключ айди есть. В рамках данной выборки его наличие нважно, так как сортировка идёт по дате, а выборка ещё по двум признакам данных. Причём, поскольку данные могут забиваться задним числом, то порядок айди с порядком дат не совпадает. Весь вопрос в том, как подобный запрос выполнить наиболее эффективно. айди используется только для показа/редактирования отдельных записей.

Prisoner
Я кажется уже выше отметил, что документацию по индексам и тому, как они влияют на скорость выборки/сортировки понял не в полном объёме. Иначе бы здесь вопрос не задавал. А EXPLAIN сейчас мой лучший и единственный друг. Просто надоело экспериментальным путём подбирать оптимальную структуру, хочется закономерностей. Видимо, единственный метод, который позволит с ними разобраться - проб и ошибок.

[mar]

ivank
так ведь - либо парсить код самого mysql, чтобы понять, как именно в ней реализованно то, или иное, либо положиться на документацию, либо - опыты на кошках (Влад, например, утверждает, что опытным путем обнаружил тормоза при работе LEFT JOIN - что-то я не припомню, чтобы в документации в этом признвались, но опыт показал). (С учетом некоторой вольности mysql в обращении c SQL вообще :)) А что именно кажется смурным в документации?

[Vlad Drakula]

mar
Влад, например, утверждает, что опытным путем обнаружил тормоза при работе LEFT JOIN - что-то я не припомню, чтобы в документации в этом признвались, но опыт показал
тормоза возникают на из-за самого JOIN (на важно какого) а из-за процессов которые при этом возникают: пораждается большая временная таблица, которая зачастую записывается на жесткий диск + выборки по большой таблице а еще возможно и без индексов идут не очень быстро...

[mar]

Vlad Drakula
ага, спасибо. Короче, твой опыт выявил локальный глюк mysql , т.е. говорит о том, что метод научного тыка бывает полезен (теория тут явно бы не подошла) :)