оптимизация MySQL... как заставить таблицу использовать индекс

[slach]

оптимизация MySQL... как заставить таблицу использовать индекс

имеется тупейший INNER JOIN по трем табличкам

EXPLAIN SELECT *
FROM T_EST_ORDER a,
T_EST_CUSTOMER b,
T_EST_FILE_DOWNLOAD c
WHERE (a.customer_fk=b.customer)
AND (c.member_fk=b.member_fk)

получаю

********************************************************************************************************************
* id * select_type * table * type * possible_keys     * key             * key_len * ref        * rows * Extra      *
*  1 *      SIMPLE *     b *  ALL * PRIMARY,member_idx*                 *         *            * 9415 *            *
*  1 *      SIMPLE *     a *  ref * Reference_42_FK   * Reference_42_FK * 5       * b.customer *    1 * Using where*
*  1 *      SIMPLE *     c *  ref * member_idx        * member_idx      * 5       * b.member_fk*    2 * Using where*
********************************************************************************************************************

вопрос, как заставить таблицу B использовать индекс ?? member_fk или еще что ???
или я савсем ничего не понимаю в том, как должен в данном случае работать JOIN?

 

[ONK]

slach, ты думаеш, что привёл достаточно информации?

 

[slach]

ONK да, я думаю что привел достаточно информации
ты не умеешь читать ПЛАН ?

еще раз, словами:
есть таблица
она связана с двумя другими таблицами
по двум разным полям
customer и member_fk

на каждое из этих полей в этой таблице есть индекс

в связанных таблицах
также есть индексы на поля учавствующие в JOIN

меня интересует почему стоит TYPE = ALL для таблицы b (T_EST_CUSTOMER)

почему MySQL не может например, сначала выбрать с использованием индексов по полю customer
а потом выбрать из оставшегося набора данных с использованием member_fk

всякие варианты с INNER JOIN я пробовал, план запроса остается тем же самым

есть вариант попробовать использовать HEAP временную таблицу...

 

[DiTHER]

потому что ты не понимаешь как работают индексы.
В одном запросе может использоваться только один индекс. Правда это может быть индекс на нескольких полях.

представь себе

допустим индекс по айдишникам

1
2
3
4
...

а индекс по дате

2005-10-10
2005-10-11
2005-10-12
...

если нужно выбрать где id = 2 и дата = 2005-10-12 ты даже в уме использовать обе эти таблицы не сможешь. Когда есть несколько индексев. mysql пишет в explain possible_keys все ключи какие есть. и выбирает тот, выборка по которому даст меньше всего результатов (будет самой результативной). Чтобы проще было раскопать оставшуюся кучу.

Чем больше индексов тем больше вероятность что mysql выберет нужный (это когда полей действительно много и поиск может осуществляться решительно по всем). Но более 1-2 практически не годится для баз где юзеры могут что то вносить. Больше индексов - больше время внесения.

Часто убыстрить запрос получается когда сделать индекс на полях которые участвуют в сортировке (при этом не допускается перемешивание asc и desc).
В противном случае (когда сортировка не использует индекс) explain в графе Extra покажет using filesort. Не забываем, что сортировка - одна из самых ресурсоёмких операций. Делаем выводы.

советую почитать про индексы на dev.mysql.com. там все расписано ну прямо оооочень подробно

-~{}~ 28.06.05 16:28:

и где ты вычитал эту глупую формулировку "план запроса"?

для нанизывания данных одной таблицы по номеру и айди другой использовать left join

-~{}~ 28.06.05 16:31:

и по твоему запросу ничего подсказать невозможно пока ты не расскажешь какие есть индексмыы

EXPLAIN SELECT b.id as id, a.customer_fk as a, c.member_fk as c
FROM b
LEFT JOIN a on a.customer_fk = b.customer_fk
LEFT JOIN c on c.member_fk = b.member_fk;

 

[chira]

slach
можешь привести количество записей в каждой из таблиц?
скажи причину, почему MySQL должен использовать индекс?

 

[slach]

ладно =) RTFM
как всегда
все варианты пришли самостоятельно

FORCE INDEX
и через временную таблицу
(
CREATE TEMPORARY TABLEtmp...

INSERT INTO tmp... SELECT FROM customer INNER JOIN order ON
и т.п.
SELECT FROM tmp INNER JOIN download
)
завтра с утра поиграюсь

2DiTCHER
не бузи пожалуйста, я пользуюсь общепринятой терминологией:
http://www.google.com/search?q=план+запроса+sql&num=0&ie=utf-8&oe=utf-8

я очень хорошо понимаю, как работают индексы, более того, мне самому доводилось их реализовывать (пусть и не в таких областях как SQL)
в данном случае, мне было непонятно, почему mysql не использовал мои индексы и я с первой попытки не вспомнил как его заставить использовать нужный индекс ПРИНУДИТЕЛЬНО

по твоему запросу ничего подсказать невозможно пока ты не расскажешь какие есть индексмыы

перечитай еще раз мой постинг

на каждое из этих полей в этой таблице есть индекс

в связанных таблицах
также есть индексы на поля учавствующие в JOI

мне кусок SHOW CREATE TABLE показать ? чтобы ты убедился ??

для нанизывания данных одной таблицы по номеру и айди другой использовать left join

"нанизывание данных"
гЫгЫгЫ
в данном случае я использую INNER JOIN
мне нужно точное соответсвие
ты читал в документации
что SELECT * FROM t1,t2 WHERE t1.id=t2.id_fk
эквивалентент SELECT * FROM t1 INNER JOIN t2 ON (t1.id=t2.id_fk) ??

Chira
скажи причину, почему MySQL должен использовать индекс?
[/qoute]
хотя бы потому, что так делает FireBird (правда там запрос несколько другой), MSSQL
на Oracle честно говоря не пробовал (негде)

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

 

[chira]

slach

на мой взгляд очень логинчо использовать индекс, если джойнятся две таблицы и оба поля в связке join имеют индекс
разве нет ?

никакой логики
ты представляешь примерный алгоритм для соединения двух таблиц?
тебе EXPLAIN выдаёт таблицы по порядку их соединения
другими словами читаем записи из первой таблицы, потом ищем соответсвующие во второй ...
так вот, если у тебя нет дополнительного условия id=10 или name='Вася' для которого можно использовать индекс, то он и не используется.
в твоём случае MySQL последовательно читает записи из таблицы b и используя индексы из других таблиц, соединяет соответствующие строки ...
если для первой таблицы будет ещё читаться индекс, то это вряд ли ускорит общую производительность ...

 

[slach]

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

в общем похоже вариант с работой через временную таблицу, в которой сохраняются нужные поля INNER JOIN между a и b
а потом работа с ней через INNER JOIN tmp и c
оптимальный вариант

 

[camka]

План запроса твоего первого поста совершенно правильный и оптимальный. Поскольку константных условий выборки у тебя не имеется (a la id=xxx), то по любому mysql должен будет взять одну из таблиц полностью и сопоставлять _каждой_ записи из этой таблицы соответствующие записи остальных таблиц. Именно по этому то и присутствует столь пугающее тебя число 9415. Собственно, оно и показывает общее количество записей в таблице b.

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

На мой взгляд объединение трех таблиц, описанное тобой изначально, вполне оптимально.

-~{}~ 29.06.05 11:36:

MySQL resolves all joins using a single-sweep multi-join method. This means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the third table, and so on. When all tables are processed, it outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.