Как оптимизировать/объединить запрос sql (полнотекстовый поиск)?

Question

[Алексей]

Нужно произвести полнотекстовый поиск как по заголовкам товаров, так и по заголовкам категорий. В случае совпадения будет возвращена выборка id этих товаров.

Вот поиск по заголовкам категорий

SELECT `id` FROM `object` obj
	JOIN `category` cat ON MATCH(cat.`title`) AGAINST (?)
	JOIN `obj2cat` obj2 ON obj2.`cat` = cat.`tid` 
	WHERE obj.`id` = obj2.`obj`

Выполняется за сотые доли секунды

Поиск по заголовкам товаров

SELECT `id` FROM `object` obj  WHERE MATCH(obj.`title`) AGAINST (?)

Также выполняется быстро

Но если их объединить

SELECT `id` FROM `object` obj
	JOIN `category` cat ON MATCH(cat.`title`) AGAINST (?)
	JOIN `obj2cat` obj2 ON obj2.`cat` = cat.`tid` 
	WHERE (
		obj.`id` = obj2.`obj`
		OR
		MATCH(obj.`title`) AGAINST (?)
	)

Время выполнения составляет пару десятков минут :(
Где я ошибся? Индексы на месте

EXPLAIN последнего запроса

array (
  0 => 
  array (
    'id' => 1,
    'select_type' => 'SIMPLE',
    'table' => 'cat',
    'type' => 'fulltext',
    'possible_keys' => 'PRIMARY,title',
    'key' => 'title',
    'key_len' => '0',
    'ref' => '',
    'rows' => 1,
    'Extra' => 'Using where',
  ),
  1 => 
  array (
    'id' => 1,
    'select_type' => 'SIMPLE',
    'table' => 'obj',
    'type' => 'ALL',
    'possible_keys' => 'PRIMARY',
    'key' => NULL,
    'key_len' => NULL,
    'ref' => NULL,
    'rows' => 4604,
    'Extra' => '',
  ),
  2 => 
  array (
    'id' => 1,
    'select_type' => 'SIMPLE',
    'table' => 'obj2',
    'type' => 'ALL',
    'possible_keys' => 'obj',
    'key' => NULL,
    'key_len' => NULL,
    'ref' => NULL,
    'rows' => 14676,
    'Extra' => 'Using where',
  ),
)

Comments

[FanatPHP]

уберите тег mysqli пожалуйста

Answer 1

[Игорь]

попробуйте

SELECT `id` FROM `object` obj
  JOIN `category` cat ON MATCH(cat.`title`) AGAINST (?)
  JOIN `obj2cat` obj2 ON obj2.`cat` = cat.`tid` 
  WHERE obj.`id` = obj2.`obj`
UNION
SELECT `id` FROM `object` obj  WHERE MATCH(obj.`title`) AGAINST (?)

Comments

[Алексей]

Все равно, по сути это два отдельных запроса, не могу понять, почему объединение условий приводит к такому замедлению запроса

[Игорь]

я не берусь утверждать, но вообще у вас странный запрос, первый. Таблицу catagory вы джоините с object, но при джоина как такового у вас нет - выполняется полнотекстовый поиск. Я бы написал так

select `id` FROM `object` obj, `category` cat, `obj2cat` obj2
where obj.`id` = obj2.`obj` and obj2.`cat` = cat.`tid`
and MATCH(cat.`title`) AGAINST (?)

а в итоге получилось бы так:

select `id` FROM `object` obj, `category` cat, `obj2cat` obj2
where obj.`id` = obj2.`obj` and obj2.`cat` = cat.`tid`
and (
MATCH(cat.`title`) AGAINST (?)
or
match(obj.title`) against(?)
)

[Алексей]

Игорь: да, спасибо большое, то, что нужно.

Я новичок в sql запросах, многие аспекты не совсем понятны, еще раз спасибо за помощь!

Answer 2

[Melkij]

Первый раз вижу джойн по полнотекстовому поиску.
Хотя бы так попробуйте:

SELECT `id` FROM `object` obj
  JOIN `category` cat ON obj.`id` = obj2.`obj`
  JOIN `obj2cat` obj2 ON obj2.`cat` = cat.`tid` 
  WHERE (
    MATCH(cat.`title`) AGAINST (?)
    OR
    MATCH(obj.`title`) AGAINST (?)
  )

Если версия mysql актуальная (емнип, в 5.5 правили подзапросы). то можно попробовать:

SELECT `id` FROM `object` obj  WHERE MATCH(obj.`title`) AGAINST (?) or tid in (SELECT `tid` FROM `category` cat
  JOIN `obj2cat` obj2 ON obj2.`cat` = cat.`tid` -- мог неверно понять вашу структуру
  WHERE MATCH(cat.`title`) AGAINST (?) )

Comments

[Алексей]

Переделал запрос под такой
SELECT `id` FROM `object` obj WHERE
MATCH(obj.`title`) AGAINST (?)
OR
`id` IN (
SELECT `obj` FROM `obj2cat` obj2
JOIN `category` cat ON cat.`tid` = obj2.`cat`
WHERE MATCH(cat.`title`) AGAINST (?)
)
Все равно долго выполняется

В вашем способе с джойнами сервер ругается на 2-ю строку, мол неизвестный алиас obj2

Структура таблиц такая

category:
tid | title

object:
id | title

obj2cat:
obj | cat

obj2cat соотносит объекты (obj->id) и категории (cat->tid), т.к. у категории может быть несколько объектов, так и объект может состоять в нескольких категориях

[Melkij]

Логично. Копипастил ваш запрос. Тогда как-то так:
SELECT `id` FROM `object` obj
JOIN `obj2cat` ON obj2cat.obj = obj.id
JOIN `category` cat ON cat.tid=obj2cat.cat
WHERE (
MATCH(cat.`title`) AGAINST (?)
OR
MATCH(obj.`title`) AGAINST (?)
)

У меня есть сомнения по поводу пересечения двух полнотекстовых индексов. union как раз проще и почти наверняка быстрее за счёт более простого и, главное, независимого по индексам плана запроса.

[Алексей]

Melkij: Через union, конечно, проще, но я хотел разобраться, что было в том запросе неверно. Мне уже написали верный ответ, но все равно спасибо.

Answer 3

[Rsa97]

SELECT `obj`.`id` 
  FROM `object` AS `obj`
  JOIN `obj2cat` ON `obj2cat`.`obj` = `obj`.`id`
  JOIN `category` AS `cat` ON `cat`.`id` = `obj2cat`.`cat`
  WHERE MATCH(`cat`.`title`) AGAINST (?)
    OR MATCH(`obj`.`title`) AGAINST (?)