Скорость выборки записей из очень бАААльшой таблицы...

[Dr_Jones]

Скорость выборки записей из очень бАААльшой таблицы...

mysql 4.1.8
имеется таблица table из двух полей, pole_1 и pole_2, MEDIUMINT(6) и SMALLINT(5) соответсвенно.
в таблице около 10 млн записей, индекс один на оба поля.
запрос:
SELECT pole_1 FROM table WHERE pole_2 IN(...)
занимает около 10 секунд при двух значениях в IN
очень долго это...вот как бы побыстрее?
вариант реструктуризации базы крайне не желателен, если, кнечно, он не единственный
спасибо.

 

[Falc]

Dr_Jones
>>SELECT pole_1 FROM table WHERE pole_2 IN(...)
Для данного запроса нужен индекс на поле pole_2

 

[ForJest]

Dr_Jones
что показывает EXPLAIN?

 

[Dr_Jones]

прошу прощения за ошибку.
индекс на поле pole_2.
объем таблицы большой из-за того, что основное кол-во записей - это записи с значеиями pole_2 = от 1 до 10(в среднем на каждое из этих значений 600-700 тыс записей)
если в IN указать значения, сумма записей которых больше приблизительно 2 млн-ов (кнечно не факт, что это причина), то тип связывания меняется c range на ALL, key становиться пустым, USE INDEX, FORCE INDEX ничего не меняет(может и не должно...), ref "всегда" пусто

 

[Фанат]

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

 

[Dr_Jones]

в общем
таблицы tabl1 и tabl2
tabl1 - та самая таблица, о которой шла речь выше, pole_1 в ней меняется на tabl2_id
tabl2 - id, id_tovar, date_begin, date_end

запрос выглядит так

SELECT DISTINCT tabl2.id_tovar
FROM tabl1, tabl2
WHERE tabl1.pole_2 IN (...)
AND tabl2.id = tabl1.tabl2_id
AND tabl2.date_begin <= DATE_FORMAT('...','%Y-%m-%d')
AND tabl2.date_end >= DATE_FORMAT('...','%Y-%m-%d')
GROUP BY tabl1.id
HAVING COUNT(*)=кол-во значений в IN, не лежащих в диапозоне 1-10;

замутно, но работает, но долго...

 

[ForJest]

http://dev.mysql.com/doc/refman/4.0/ru/query-speed.html

 

[Falc]

Dr_Jones
Может тебе индекс по дате использовать? Много у тебя записей под условия с датами попадает?

 

[chira]

Dr_Jones

сколько записей в tabl2?

можешь сделать EXPLAIN приведённого тобой запроса?
судя по твоим рассуждениям у тебя записи из tabl1 соединяются с записями из tabl2, а не наоборот?
нужно поробовать добиться изменения порядка просмотра таблиц.
1. согласно фильтрам по полям date_begin date_end выбираем строки из tabl2
2. находим в таблице tabl1 соответствующие найденным, используя составной индекс по полям pole_1,pole_2

 

[Dr_Jones]

Falc
Индекс по дате используется.
Под условия с датами попадает, в зависимости от введенных параметров, от 1 до всех(820240) записей

chira
в tabl1 - 9842904 индексы: pole_2 тип: index кол-во: 23214;tabl2_id тип: index кол-во: 820242;
в tabl2 - 820240 индексы: primary(поле id) тип: primary кол-во 820242;id(поля tovar_id, date_begin, date_end) тип: index кол-во: 410121;


EXPLAIN
самый тяжелый вариант, когда по дате встряли все и в in максимальное кол-во значений pole_2 т.е 22 из них 10, на которые приходится 70% записей в таблице tabl1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tabl2 ALL PRIMARY NULL NULL NULL 820242 Using where;Using temporary;Using filesort

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tabl1 ref pole_2,tabl2_id tabl2_id 3 tabl2.id 12 Using where;Distinct

меняю порядок просмотра STRAIGHT_JOIN

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tabl1 ALL pole_2,tabl2_id NULL NULL NULL 9842904 Using where;Using temporary;Using filesort

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tabl2 eq_ref PRIMARY PRIMARY 3 tabl1.tabl2_id 1 Using where

ставлю FORCE INDEX(pole_2) rows - 6993626 все равно много...

 

[chira]

Dr_Jones

1. чего-то не сходиться
пишешь
...
GROUP BY tabl1.id
...
а в tabl1 только два поля pole_1, pole_2 ?

2. в таблице tabl2 у тебя составной индекс (tovar_id, date_begin, date_end)?
3. и наконец ты хочешь выбрать все 820240 записи из tabl2 связать их с tabl1, всё это сгруппировать, отфильтровать и всё это за секунду?

вообще какая у тебя задача?
если магаз какой-нибудь или склад с миллионом товаров, то купите мощный сервер, поставте Oracle и гоняйте миллионы товаров ...
или меняй алгоритм ...

 

[Apache_xp]

Действительно, если не секрет = какой смысл впринципе в этом запросе?

 

[Dr_Jones]

chira

да, действительно косяк, извиняюсь
pole_1 я изменил на tabl2_id в сообщении с запросом

т.е
SELECT DISTINCT tabl2.id_tovar
FROM tabl1, tabl2
WHERE tabl1.pole_2 IN (...)
AND tabl2.id = tabl1.tabl2_id
AND tabl2.date_begin <= DATE_FORMAT('...','%Y-%m-%d')
AND tabl2.date_end >= DATE_FORMAT('...','%Y-%m-%d')
GROUP BY tabl1.tabl2_id
HAVING COUNT(*)=кол-во значений в IN, не лежащих в диапозоне 1-10;

т.е проблема в логике, адназначна(по той инфе, которую я предоставил)?

задача - магазин, 200 тыс. товаров.
запрос для поиска товара по 12-ти параметрам и по дате выпуска, у каждого товара в таблице tabl2 может быть любое кол-во записей и к каждой такой записи привязано столько записей в таблице tabl1, сколько существует параметров.
такая штука в таком виде действительно в данный момент работает на Oracle и данный запрос работает в пределах секунды...
появилась нужда на MySQL и php все это сделать и для простоты обмена данными между базами оставить одну структуру

 

[Falc]

chira
Не думаю что оракл тут поможет, так как групировка там не шибко быстрее работает.

Dr_Jones
Тебе нужно менять ограничивающие условия, групировка милионов строк бытрой никак не будет.

-~{}~ 25.11.05 11:43:

Dr_Jones


SELECT DISTINCT tabl2.id_tovar
FROM tabl1, tabl2
WHERE tabl1.pole_2 IN (...)
AND tabl2.id = tabl1.tabl2_id
AND tabl2.date_begin <= DATE_FORMAT('...','%Y-%m-%d')
AND tabl2.date_end >= DATE_FORMAT('...','%Y-%m-%d')
GROUP BY tabl1.tabl2_id
HAVING COUNT(*)=кол-во значений в IN, не лежащих в диапозоне 1-10;

В таком запросе таблица tabl2 явно лишняя. Вытащи нужные товары для начала без нее.
И индекс тебе нужно сделать составной: pole_2, tabl2_id

 

[chira]

Falc
Просто думать - это мало, нужно знать.
В Оракле существуют дополнительные возможности по оптимизации: Partitions, Clasters, индексы для двух таблиц ...

Dr_Jones

не знаю на сколько сильно это поможет

SELECT DISTINCT tabl2.id_tovar
FROM tabl1, tabl2
WHERE tabl1.pole_2 IN (...)
AND tabl2.id = tabl1.tabl2_id
AND tabl2.date_begin <= DATE_FORMAT('...','%Y-%m-%d')
AND tabl2.date_end >= DATE_FORMAT('...','%Y-%m-%d')
GROUP BY tabl2.id
HAVING COUNT(*)=кол-во значений в IN, не лежащих в диапозоне 1-10;

цель этого всего убрать лишнюю сортировку

ну и как Falk написал создать составной индекс для tabl1, только такой (tabl2_id, pole_2)

 

[Falc]

chira
>>только такой (tabl2_id, pole_2)
Такой индекс поможет если раскручивать запрос с tabl2

Я же считаю что эффективнее сначало взять только таблицу tabl1 и только по ней отобрать нужные товары. Причем возможно наиболее быстрым способом тут будет вообще фулскан. А потом уже присоеденить товары (tabl2).

 

[chira]

Falc

ты ничего не путаешь?
tabl1 - это таблица с 10 млн. записей и критерий отбора по ней IN(....)?
такой подход может быть оправдан, если для этой таблице будет эффективный фильтр и в выборке мы получим очень малое кол-во строк, намного меньшее чем в tabl2
для tabl1 есть только один фильтр: IN(...), но он далёк от желаемой эффективности.

 

[Falc]

chira
>>tabl1 - это таблица с 10 млн. записей и критерий отбора по ней IN(....)?
IN + HAVING COUNT(*)=const

-~{}~ 28.11.05 10:32:

Соединение будет происходить уже не с 10 млн. записей а с гораздо меньшим и GROUP BY будет делать сортировку на меньшем кол-ве полей.

-~{}~ 28.11.05 11:42:

Dr_Jones
Вобщем попробуй вот такой запрос:

SELECT  tabl2_id
FROM
(SELECT DISTINCT tabl1.tabl2_id
FROM tabl1
WHERE tabl1.pole_2 IN (...)
GROUP BY tabl1.tabl2_id
HAVING COUNT(*)=кол-во значений в IN, не лежащих в диапозоне 1-10 ) t
, tabl2
WHERE
tabl2.id = t.tabl2_id
AND tabl2.date_begin <= DATE_FORMAT('...','%Y-%m-%d')
AND tabl2.date_end >= DATE_FORMAT('...','%Y-%m-%d')

Да и индекс должен быть либо составным pole_2, tabl2_id либо его не должно быть вообще (будут постоянные полные переборы, что не очень хорошо)

 

[chira]

интересно было бы узнать результат ...

 

[Leon_V]

раньше писал под чужим ником

вот что "вышло".

SELECT DISTINCT tabl2.id_tovar
FROM tabl1 d2, tabl1 d1, tabl1 d3, tabl2
USE INDEX ( Index_4 )
WHERE d2.tabl2_id = tabl2.id
AND tabl2.date_begin <= date_format( '...', '%Y-%m-%d' )
AND tabl2.date_end >= date_format( '...', '%Y-%m-%d' )
AND d1.tabl2_id = d2.tabl2_id
AND d1.pole_2 =13
AND d2.tabl2_id = tabl2_id
AND d2.pole_2 =272
AND d3.pole_2
IN ( 2, 1, 3, 10, 6, 8, 5, 7, 4, 8367, 2312, 20002, 25074, 25061, 25067, 25060, 25065, 25033 )
GROUP BY d3.tabl2_id
HAVING count( * ) =9


индексы

tabl1
PRIMARY PRIMARY tabl2_id,pole_2
index_2 UNIQUE pole_2,tabl2_id

tabl2
index_4 index id,date_begin,date_end,tovar_id
tovar_id index tovar_id


такой запрос первый раз выполняется в районе 20 секунд, предыдущие далеко за 100...
все последующие разы в районе 1 секунды, даже если изменять параметры, т.е насколько я понимаю это не query cache,
а индексы кэшируются.
в предыдущих постах я всем вам врал, что mysql 4.1...не верьте мне, 4.0
можно ли как-нибудь сделать предзагрузку индексов в 4.0 как LOAD INDEX INTO CACHE в более свежих версиях или это не вариант?