Индексы для поиска.

[Ilya Indigo]

БД MariaDB 5.5.23
Таблица `table` на движке InnoDB условно имеет, по мимо прочего, поля VARCHAR(32) `f1`,`f2`,`f3`,`f4` и `t`.
Необходимо организовать поиск по этим полям, условно 'search' со следующим, приоритетом вывода...
1 Все записи, где `t`='search'
2 Все записи, где `f1`='search' && `f2`='search' && `f3`='search' && `f4`='search'
3 Все записи, где `f1`='search' || `f2`='search' || `f3`='search' || `f4`='search'

Решаемо примерно так:

SELECT * FROM `table` WHERE `t`='search' ORDER BY `order` UNION SELECT * FROM `table` WHERE `f1`='search' && `f2`='search' && `f3`='search' && `f4`='search' ORDER BY `order` UNION SELECT * FROM `table` WHERE `f1`='search' || `f2`='search' || `f3`='search' || `f4`='search' ORDER BY `order`;

Проблема в том, что я не уверен как правильно задать индексы для моего случая.
Нужно задать:
1 Для каждого поля в отдельности?
2 Для `t` и составной для (`f1`,`f2`,`f3`,`f4`)?
3 И для каждого в отдельности, и составной для (`f1`,`f2`,`f3`,`f4`)?
4 Или вы видите как эту задачу можно решить проще (более производительнее и быстрее)?

Пожалуйста помогите расставить индексы или предложите лучшее решение, и обоснуйте выбор

 

[Redjik]

ну какие чаще выборки?
я бы составной делал

 

[Dovg]

Если у тебя есть индекс (`f1`,`f2`,`f3`,`f4`), то отдельный индекс на f1 как правило не нужен.

 

[Sufir]

А почему бы с EXPLAIN не поэкспериментировать с разными комбинациями индексов?

 

[Ilya Indigo]

>>ну какие чаще выборки?
Я так думаю что чем выше приоритет, тем меньше выборок (возвращаемых строк по запросу).
Но я всё равно всегда все 3 запроса посылаю через UNION, ранее упомянутым кодом, и на сколько я понимаю, без разницы сколько строк будет чаше возвращено, главное что в любом случае без индексов поля будут перебираться. Если, конечно, я правильно понимаю работу индексов.

Спрошу немного по-другому, если будет составной индекс, так как поля выбираются через &&, есть ли смысл делать ещё и одинарный для каждого поля, если эти поля могут выбираться по отдельности (через ||)?

 

[Redjik]

если ты релевантность высчитываешь через union, то не лучше ли fulltext сделать по 4м полям этим?

 

[alekciy]

2 Для `t` и составной для (`f1`,`f2`,`f3`,`f4`)?

Да. Составной индекс в условии будет работать пока там есть все поля из индекса слева направо: How MySQL Uses Indexes (думается в 5.5 механизм тот же).

MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

3 И для каждого в отдельности, и составной для (`f1`,`f2`,`f3`,`f4`)?
4 Или вы видите как эту задачу можно решить проще (более производительнее и быстрее)?

Случай 3 проигрывает 2 на вставках и обновлениях, т.к. нужно пересчитывать большее количество индексов.

Таки поддержу Sufir - EXPLAIN вам в руки!

 

[alekciy]

если ты релевантность высчитываешь через union, то не лучше ли fulltext сделать по 4м полям этим?

Если судить по описанию, то fulltext поиск там не нужен.

 

[Sufir]

А в InnoDB уже есть FULLTEXT?

 

[Ilya Indigo]

Спасибо всем за ответы, про EXPLAIN не знал, с MATCH() не работал, но судя по её возможностям - это то, что мне в идеале нужно, если я правильно понял как она работает.
В XtraDB полнотекста конечно же нет, но никто не мешает мне альтерануть в Aria, а там уж поэкспериментировать с EXPLAIN

 

[Redjik]

можно сфинкс прикрутить или триггерами копировать в myisam