Индексы и OR

[Vsevolod]

Индексы и OR

Как заставить MySQL использовать индексы в выборке с OR:

select .. where n1 like 'f%' or n2 like 'f%'

 

[zerkms]

с такой конструкцией индексы вполне нормально работают
уточни ситуацию

 

[Vsevolod]

У меня не работают.

Если сделать индекс на 2 поля - они работают только если

n1 like 'f%' AND n2 like 'f%'

А

n1 like 'f%' AND n2 like 'f%' не работает. в общем и не должен..

Если сделать 2 отдельных индекса по n1 и n2 в OR они тоже не работают.

n1 и n2 - varchar(255)

 

[walrus]

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

v. 3.23 - 4.1
However, the name index is not used in the following queries:

SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';

 

[zerkms]

упс, прочитал невнимательно (показалось - что дважды n1)
судя по мануалу, угу, нельзя ибо "No index spans all rows" (с)

 

[Vsevolod]

Автор оригинала: walrus
в мануале к составным индексам написано, что как раз в конструкции с OR индексы не используются

совершенно верно. есть какие-нибудь варианты, как все-таки с индексами запрос сделать?

 

[walrus]

сделай через UNION, тогда индексы должны заработать
сор, фигню сначала про интерсект написал


Vsevolod
А сколько времени уходит на выборку?

 

[chira]

Vsevolod

альтернатива условию OR в WHERE - это использование UNION
получиться два селекта, но каждый будет использовать индекс
выбери для себя оптимальный вариант ...

 

[Vsevolod]

Автор оригинала: walrus
сделай через UNION, тогда индексы должны заработать
сор, фигню сначала про интерсект написал


Vsevolod
А сколько времени уходит на выборку?

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

-~{}~ 05.04.07 17:24:

а в mysql 5.0 в where уже можно использовать несколько несоставных ключей. верно?

-~{}~ 05.04.07 17:55:

c union работает. explain показывает, что будут использоваться индексы и соответственно малое число строк, но при этом, с union работает значительно дольше чем аналогичная выборка без него. где-то в 3 раза дольше.

 

[chira]

Vsevolod

простой UNION подполагает дополнительную сортировку для отфильтровывания одинаковых строк ...
поэтому тормозит

 

[algo]

Иначе говоря, попробуй UNION ALL

 

[walrus]

Автор оригинала: Vsevolod
если обращений мало, то не много. но когда параллельно идет куча запросов, то просмотр 90тыс строк уже очень чувствуется.

-~{}~ 05.04.07 17:24:

а в mysql 5.0 в where уже можно использовать несколько несоставных ключей. верно?

не уверен, но по работе EXPLAIN mysql может выбирать из нескольких индексов, но использовать будет только один. Для этого как раз и составные индексы используются. Если не прав, то поправьте.

Похоже, что в версиях 5.х используется новый алгоритм работы с несоставными индексами, т.е. можно использовать несколько простых индексов
http://mysql.org/doc/refman/5.0/en/index-merge-optimization.html

 

[Vsevolod]

Автор оригинала: algo
Иначе говоря, попробуй UNION ALL

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

-~{}~ 05.04.07 22:09:

Автор оригинала: walrus
не уверен, но по работе EXPLAIN mysql может выбирать из нескольких индексов, но использовать будет только один. Для этого как раз и составные индексы используются. Если не прав, то поправьте.

Похоже, что в версиях 5.х используется новый алгоритм работы с несоставными индексами, т.е. можно использовать несколько простых индексов
http://mysql.org/doc/refman/5.0/en/index-merge-optimization.html

в m4 насколько я понимаю данные из составных индексов используются в порядке их указания в индексе. т.е. если там индекс для n1, n2 то при запросе where n2 like '...' индекс использоваться не будет, а при запросе where n1 like '...' and n2 like '...' будет.

m5 ни у кого не установлен?

 

[chira]

Vsevolod

скажи сколько твой запрос возвращает записей из 90 тыс., 5 - 10% ?

 

[walrus]

Vsevolod
у меня в 5 версии составные индексы работают так же, как и в 4
Но после перехода на 5 версию у меня выборка из 600тыс записей по условию like '%xxx%' стала гораздо быстрее, хотя как раз в моем случае индекс по текстовому полю точно не работает и работать не должен

а какую длину индекса ты используешь?

 

[Vsevolod]

Автор оригинала: walrus
Vsevolod
у меня в 5 версии составные индексы работают так же, как и в 4
Но после перехода на 5 версию у меня выборка из 600тыс записей по условию like '%xxx%' стала гораздо быстрее, хотя как раз в моем случае индекс по текстовому полю точно не работает и работать не должен

а какую длину индекса ты используешь?

я экспериментировал, поскольку у меня сейчас разные выборки. я делал длиной 1 символ для выборки like 'f%' (по первому символу) и длиной равной длине поля. по времени доступа с union результат одинаковый, а в or они не сработали...

как вмечатления от 5-й? проблем с переходом нет?

 

[walrus]

сейчас запустил на свое базе след запросы
1. where name like 'a%' or name like 'b%';
Был полный скан таблицы в 600тыс записей, время выборки 5 сек.

2. такой же запрос ,но с union
Использовались индексы, время выполнения - 14 сек.

3. union all
Использовались индексы, время выполнения - 10 сек.

На сервере сейчас минимальная нагрузка, стоит только одна вот эта база, 2хXeon 3Ghz + 2GB памяти


Честно говоря, ожидал большего...

 

[Vsevolod]

Автор оригинала: walrus
сейчас запустил на свое базе след запросы
1. where name like 'a%' or name like 'b%';
Был полный скан таблицы в 600тыс записей, время выборки 5 сек.

2. такой же запрос ,но с union
Использовались индексы, время выполнения - 14 сек.

3. union all
Использовались индексы, время выполнения - 10 сек.

На сервере сейчас минимальная нагрузка, стоит только одна вот эта база, 2хXeon 3Ghz + 2GB памяти


Честно говоря, ожидал большего...

Прилично. У меня 2.5 сек. без индексов по 2-м полям varchar аналогичный запрос. 90.000 строк. P4 1,7 1Gb. При средней нагрузке сервера.

 

[chira]

Честно говоря, ожидал большего.

ну и сколько записей твой запрос возвращает ?

индксы эффективны только если возвращаемый результат составляет ~5% от всех данных в таблице ...

 

[Vsevolod]

Автор оригинала: chira
ну и сколько записей твой запрос возвращает ?

индксы эффективны только если возвращаемый результат составляет ~5% от всех данных в таблице ...

в зависимости от запроса 200-2000. тот что 2.5 секунды выполняется - 250 записей. в любом случае это значительно меньше 5%