Объясните про индексы

[Духовность™]

Объясните про индексы

Я никак не могу понять, в каких случаях использовать многостолбцовый индекс, а в каких раздельный!

Допустим, есть таблица:

[sql]CREATE TABLE `users_groups_operations` (
`id_group` smallint(5) unsigned NOT NULL,
`id_action` tinyint(3) unsigned NOT NULL,
`id_operation` tinyint(3) unsigned NOT NULL,
`access` tinyint(1) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;[/sql]

в код будет такая выборка:

[sql]
SELECT
`id_operation`,
`access`
FROM
`users_groups_operations`
WHERE
`id_group` = 1
AND
`id_action` = 1
[/sql]

Тут, судя по всему, надо сделать индекс на поле `id_group` и на поле `id_action`, ибо по ним идет выборка. Только какой? Многостобцовый? Или на каждое поле свой индекс?

 

[nail]

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

В общем случае лучше делать составной индекс.
"На каждое поле свой индекс" - такое работать не будет, потому что в одном where может быть использован только один индекс.

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

Вообще, чтобы в таких вопросах легко самому разбираться, надо почитать про balanced tree и selectivity.

 

[Духовность™]

потому что в одном where может быть использован только один индекс.

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

 

[nail]

а ссылку на этот пример?

 

[Духовность™]

http://www.mysql.ru/docs/man/MySQL_indexes.html

Не пример, а вот эта фраза меня смутила: mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Если по столбцам col1 и col2 существует многостолбцовый индекс, то соответствующие строки могут выбираться напрямую. В случае, когда по столбцам col1 и col2 существуют раздельные индексы, оптимизатор пытается найти наиболее ограничивающий индекс путем определения, какой индекс найдет меньше строк, и использует данный индекс для выборки этих строк.

т.е. Вы хотите сказать, что еси у нас в таблице есть два индекса, то мы не можем в выражении where ссылаться на два индексированных столбца?

 

[nail]

Оптимизатор выбирает лучший индекс - только один - и он используется для выборки.

Индекс - это дерево.
Выборка - это беготня по дереву и обращение к таблице за данными. Вот надо разобраться что это за дерево, что у нее в узлах, что в листьях, и как эта беготня происходит. После этого все вопросы отпадут.

 

[Духовность™]

ща моск сломаетса..(((

 

[nail]

А в чем вопрос-то теперь - непонятно.

> т.е. Вы хотите сказать, что еси у нас в таблице есть два индекса, то мы не можем в выражении where ссылаться на два индексированных столбца?

В where мы можем ссылаться на любой столбец, на это индексы никак не влияют.

 

[FractalizeR]

triumvirat

Многостолбцовый индекс имеет смысл делать только в случае, если поиск по таблице идет с использованием значений всех полей индекса или только его полей, расположенных в начале.

Когда вы создаете многостолбцовый индекс по полям userlogin, useremail, можете считать, что на самом деле вы создали один индекс по полю CONCAT(userlogin, useremail), т.е. индекс по склеенным значениям всех столбцев, в порядке, упомянутом при создании индекса.

Допустим мы создали такой индекс CREATE INDEX myindex ON user (userlogin, useremail);. Следующие операторы SELECT будут использовать его и быстро искать по таблице:

SELECT * FROM users WHERE userlogin = 'XXX';
SELECT * FROM users WHERE userlogin = 'XXX' AND useremail='[email protected].

А вот следующий оператор наш составной индекс использовать не сможет и ему придется сканировать всю таблицу:
SELECT * FROM users WHERE useremail = '[email protected]';


Мы могли бы, конечно, создать раздельные индексы по столбцам userlogin и usermemail, но в этом случае запрос SELECT * FROM users WHERE userlogin = 'XXX' AND useremail='[email protected] будет работать медленее, чем если бы использовался составной индекс по обоим столбцам, упомянутым в WHERE.

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

 

[Духовность™]

Спасибо.

-~{}~ 28.01.08 13:00:

Скажите, а что значит в phpMyAdmin фраза "Количество элементов - Нет" при просмотре структуры таблицы?

 

[FractalizeR]

Значит, что в индексном файлы нет записей. Это значение соответствует значению из поля Cardinality при выполнении SHOW INDEX