Подскажите, каким должен быть индекс

[Talker]

Подскажите, каким должен быть индекс

Есть табличка с информацией о людях (>300k записей). В том числе в таблице 4 поля для имени и 4 поля для фамилии (такая специфическая область, может быть у человека 4 разных имени или фамилии). Жутко тормозит поиск по имени и фамилии.
Пример запроса ("найти всех, у кого фамилия начинается на МАКСИМ и имя начинается на ПЕТР")
SELECT *
FROM person
WHERE (
famil LIKE 'максим%'
OR famil_2 LIKE 'максим%'
OR famil_3 LIKE 'максим%'
OR famil_4 LIKE 'максим%'
)
AND (
name LIKE 'петр%'
OR name_2 LIKE 'петр%'
OR name_3 LIKE 'петр%'
OR name_4 LIKE 'петр%'
)

По-разному извращался с индексами, всё равно получается тормоз. Подскажите, как по-хорошему должен выглядеть индекс для этой задачи?

 

[pilot911]

Re: Подскажите, каким должен быть индекс

Автор оригинала: Talker
Есть табличка с информацией о людях (>300k записей). В том числе в таблице 4 поля для имени и 4 поля для фамилии (такая специфическая область, может быть у человека 4 разных имени или фамилии). Жутко тормозит поиск по имени и фамилии.
Пример запроса ("найти всех, у кого фамилия начинается на МАКСИМ и имя начинается на ПЕТР")
SELECT *
FROM person
WHERE (
famil LIKE 'максим%'
OR famil_2 LIKE 'максим%'
OR famil_3 LIKE 'максим%'
OR famil_4 LIKE 'максим%'
)
AND (
name LIKE 'петр%'
OR name_2 LIKE 'петр%'
OR name_3 LIKE 'петр%'
OR name_4 LIKE 'петр%'
)

По-разному извращался с индексами, всё равно получается тормоз. Подскажите, как по-хорошему должен выглядеть индекс для этой задачи?

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

выход один - создать по первым 3-4м буквам 3-4 односимвольных поля, либо использовать SPHINX

 

[Gas]

если не рассуждать на тему, а зачем аж по 4 поля на имя и фамилию, то для начала я бы попробовал такие варианты:

1. стандартная практика оптимизации or'ов - это union'ы, то-есть создать 8 одинарных индексов на поля имени, фамилии и выполнить:

select * from
(
  (select * from person where famil LIKE 'максим%'
  union DISTINCT
  select * from person where famil_2 LIKE 'максим%'
  ..запросы с famil_3 и 4..)
  UNION ALL
  (select * from person where name LIKE 'петр%'
  union DISTINCT
  select * from person where name_2 LIKE 'петр%'
  ..запросы с name_3 и 4..)
) as t
group by id
having count(*)=2

2. использовать fulltext boolean, тогда можно или просто создать 2 составных fulltext индекса на имена и фамилии, но это подойдёт если в полях с данными только по одному слову или не важно если найдётся совпадение не в первом слове какого-то поля. Если важно, то нужно создавать или 2 доп. поля по именам и фамилиям или одно поле, где всё в куче, но к данным добавлять префиксы, например, fname_ и lname_, чтоб можно было правильно условие написать.

 

[Фанат]

Gas
а на лайк какая стандартная практика? ;-)

 

[Talker]

Автор оригинала: Gas
1. стандартная практика оптимизации or'ов - это union'ы, то-есть создать 8 одинарных индексов на поля имени, фамилии и выполнить:

Не даёт ощутимого выигрыша

Автор оригинала: Gas
2. использовать fulltext boolean, тогда можно или просто создать 2 составных fulltext индекса на имена и фамилии, но это подойдёт если в полях с данными только по одному слову или не важно если найдётся совпадение не в первом слове какого-то поля. Если важно, то нужно создавать или 2 доп. поля по именам и фамилиям или одно поле, где всё в куче, но к данным добавлять префиксы, например, fname_ и lname_, чтоб можно было правильно условие написать.

Не могу найти как сделать поиск по части фамилии
Объединил все фамилии в search_famil, сделал его fulltext-интексом, делаю запрос

SELECT * FROM person WHERE MATCH (search_famil) AGAINST ('+максим' IN BOOLEAN MODE);

Получаю всех людей по фамилии Максим, но ни одного Максимова или Максимовой. В полнотекстовом поиске можно задать маску?

 

[Gas]

а на лайк какая стандартная практика? ;-)

на like 'const%' стандартная практика - обычный btree индекс ;-)

-~{}~ 08.03.09 21:12:

В полнотекстовом поиске можно задать маску?

можно, в boolean mode доступен wildcard '*' в конце слова, '+максим*'

-~{}~ 08.03.09 21:15:

Не даёт ощутимого выигрыша

explain можно хоть поглядеть?

 

[Talker]

Автор оригинала: Gas
можно, в boolean mode доступен wildcard '*' в конце слова, '+максим*'

Красота. Время выполнения запроса сократилось на порядок. Благодарю.