Форум "DataBase и SQL"

Язык запросов баз даных

#0 UssrChild 21.10.07 12:29:04 - 24.10.07 20:00:29

Почему не используется индекс?

Ребята, помогите, пожалуйста. Уже третий день пытаюсь разобраться с проблемой и все никак... :(
Есть две таблицы: p и a.

p: num int not null primary key,
   name int index (p_name),
   parents varchar(150) not null index (p_parents)
a: num int not null primary key,
   name not null varchar(50);

Поле p.parents содержит в себе числа разделенные символом "х", например: х12х45х5435х или х456х678х3454х. Я делаю запрос:

select a.num,a.name,p.num,p.parents from prod p use index (prod_parents), allprod a where p.name=a.num and (p.parents like 'x%x6148x%' or p.parents like 'x6148x%')

целью которого есть выбрать из таблицы р все строки, в которых встречается число 6148 и связать с ними (по условию р.name=a.num) строки из таблицы а. Запрос работает необосновано долго. Выполнил для него explain extended. Вот результат:


+----+-------------+-------+--------+---------------+---------+---------+----------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+----------------+-------+-------------+
|  1 | SIMPLE      | p     | ALL    | prod_parents  | NULL    | NULL    | NULL           | 24495 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | partner.p.name |     1 |             |
+----+-------------+-------+--------+---------------+---------+---------+----------------+-------+-------------+

Индекс p_parents не используется. Вот и вопрос: почему? При использовании like, согласно документации MySQL индекс не используется при сравнении с другим полем или если строка начинается с % или _ . Помогите, пожалуйста...

P.S. Если в запросе вместо первого "х" указать что нибудь другое, например "у" - то индекс используется.

#1 Deep © 22.10.07 15:48:13

Хорошо бы конечно указывать базу, в которой все это дело варится...
Попробуй вот так:


select 
   a.num,
   a.name,
   p.num,
   p.parents 
from 
  prod p use index (prod_parents), 
  allprod a 
where 
  p.name = a.num 
and 
  (p.parents containing 'x6148x')

#2 Mystic © 22.10.07 16:56:15

x%x6148x
И какой процент записей начинается с "x"? Как я понимаю, почти все

>#1 Deep © 22.10.07 15:48:13
>Попробуй вот так:

Вообще говоря этот запрос вернет другой набор.

#3 UssrChild 22.10.07 19:47:16

>#1 Deep ©
Там я между прочим указал... Не то чтоб совсем в глаза бросается. Вобщем MySQL. А в нем containing нету. Так что не получится так.

>#2 Mystic ©
Скажу больше. Не то что почти все, а абсолютно все! Это ж разделитель.

#4 Deep © 22.10.07 20:53:35

>#2 Mystic ©
> Вообще говоря этот запрос вернет другой набор.
если не сложно, можно ли уточнить "почему?", так сказать для общего образованя.

>#3 UssrChild
ага, точно ссылка на MySQL есть, когда читал - проскочил ее (при чтении по диагонали и не такое пропустишь

)
Но опять же не указана версия сервера. А различия там есть очень существенные, если например сравнивать 3 и 5 версию. Потому, чтоб два раза не лазить за документацией, если не сложно -- уточните.

#5 UssrChild 22.10.07 21:58:11

Без проблем. Версия 5

#6 Mystic © 23.10.07 11:10:10

> Скажу больше. Не то что почти все, а абсолютно все! Это
> ж разделитель.

Тогда я тебе скажу больше: индекс таки используется. Но по порядку.

У нас есть шаблон x%x6148x. Поиск по шаблону в данном случае можно разбить на две части:
1. поиск строк, начинающихся на x (тут используется индекс)
2. сравнение по шаблону для всех строк, которые возвратил п. 1 (тит индекс не используется)

Итого, используя индекс мы получили 100% всех строк (все строки начинаются на x). Потом для этой совокупности мы начинаем применять шаблон. Т. е. в данном конкретном случае выгоды от индекса никакой

Вообще, если запрос возвращает более 90% записей, то использование индекса неэффективно для многих БД (проще уж все перебрать, чем перебирать по индексу, и для каждой строки ссылаться на основную таблицу...).

#7 Deep © 24.10.07 00:42:22

>#5 UssrChild
соглашусь с Мистиком, индекс в даном случае НИЧЕГО не дает.

Выражение x%x6148x% предполагает, что x6148x может находится в ЛЮБОМ месте строки... К примеру, есть отсортированные строки:

x6044x6234x6148x6148x
x6044x6234x6128x6044x6234x6168x6044x6234x6248x6044x6234x6178

коим образом индекс может нам помочь узнать, что вторая строка нам подходит, или что третья - не подходит, пока не проанализирует ВСЮ строку до конца?

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

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

#8 Mystic © 24.10.07 10:39:18

Я бы сказал примерно так, как написано в справке по MySQL: индекс работает до первого вхождения символа %. Т. е. в данном случае он поможет быстро отделить те записи, что начинаются на x, от тех записей, что начинаются с другого символа. А вот следи записей, который начинаются на x индекс не поможет.

А чем лепить такой формат строки, не лучше ли просто ввести дополнительные поля?

#9 UssrChild 24.10.07 20:00:29

Ну вот хотелось проверить будет ли так лучше жить... :) Оказывается что нет. Спасибо за помощь

Написать ответ