Оптимизация полнотекстового поиска mysql

Я хочу сделать поиск с полным текстом в моей сети. Мне нужен поиск с разбиением на страницы. моя база данных насчитывает 50 000+ строк / таблицы. Я изменил таблицу и сделал (title,content,date) индексом. таблица всегда обновляется, все равно есть id столбца, который автоматически увеличивается. и последняя date всегда находится в конце таблицы.

 date varchar(10) title text content text 

но общее время запроса будет стоить 1.5+ seconds . Я просматриваю многие статьи через google, некоторые писали, что только длина слова «Длина поля Index может помочь в поиске быстрее. но как text тип, он can not изменить определенную длину (я попробовал ALTER TABLE table_1 CHANGE title title TEXT(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , не работает)

 date varchar(10) title text(500) content text(1000) 

поэтому, кроме скрипта Sphinx и третьей части. как оптимизировать полнотекстовый поиск только с sql? код запроса здесь:

 (SELECT title,content,date FROM table_1 WHERE MATCH (title,content,date) AGAINST ('+$Search' IN BOOLEAN MODE)) UNION (SELECT title,content,date FROM table_2 WHERE MATCH (title,content,date) AGAINST ('+$Search' IN BOOLEAN MODE)) Order By date DESC 

Благодарю.

Основываясь на последующих комментариях вопроса, у вас есть индекс btree для ваших столбцов, а не полный текстовый индекс.

Для MATCH (название, контент) для поиска вам понадобятся:

 CREATE FULLTEXT INDEX index_name ON tbl_name (title,content); 

Я не уверен, что он примет поле даты там (последнее, вероятно, не имеет отношения к делу).

У меня есть всеобъемлющий план, чтобы вы максимально оптимизировали MySQL для индексации FULLTEXT

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

На протяжении многих лет это раздражало некоторых людей из-за незнания того, что более 600 слов исключены из индекса FULLTEXT .

Вот табличный вид этих стоп-слов .

Существует два способа обойти это

Вариант обхода 1) Создайте собственный список заметок.

Фактически вы можете отправить mysql список ваших предпочитаемых стоп-слов. Вот значение по умолчанию:

 mysql> show variables like 'ft%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | +--------------------------+----------------+ 5 rows in set (0.00 sec) 

ОК, не будем создавать наш список. Обычно я устанавливаю английские статьи как единственные временные слова.

 echo "a" > /var/lib/mysql/stopwords.txt echo "an" >> /var/lib/mysql/stopwords.txt echo "the" >> /var/lib/mysql/stopwords.txt 

Затем добавьте параметр /etc/my.cnf плюс разрешение 1-буквенного, 2-буквенного и 3-буквенных слов

 [mysqld] ft_min_word_len=1 ft_stopword_file=/var/lib/mysql/stopwords.txt 

Наконец, перезапустите mysql

 service mysql restart 

Если у вас есть таблицы с уже установленными индексами FULLTEXT, вы должны удалить эти индексы FULLTEXT и создать их снова.

Вариант обхода 2) Перекомпилируйте исходный код

Имя файла – storage / myisam / ft_static.c. Просто измените структуру C, которая содержит более 600 слов, чтобы она была пустой. Весело перекомпилировать !!!

Теперь, когда конфигурация FULLTEXT затвердела, вот еще один важный аспект:

Напишите правильные рефакторизованные запросы, чтобы оптимизатор запросов MySQL работал правильно !!!

То, что я сейчас упоминаю, действительно недокументировано: всякий раз, когда вы выполняете запросы, которые делают JOINs, и предложение WHERE содержит функцию MATCH для поиска FULLTEXT, это приводит к тому, что оптимизатор запросов MySQL обрабатывает запрос, как полное сканирование таблицы, когда дело доходит до поиска столбцы, введенные в индекс FULLTEXT. Если вы планируете запрашивать таблицу с использованием индекса FULLTEXT, ALWAYS реорганизует ваш запрос, чтобы поиск FULLTEXT возвращал только ключи в подзапросе и соединял эти ключи с вашей основной таблицей . В противном случае индекс FULLTEXT помещает оптимизатор запросов MySQL в штопор.

Дополнительные идеи по оптимизации полнотекстового поиска в MySQL см. В разделе Как оптимизировать полнотекстовый поиск MySQL Boolean? (Или что заменить его?) – C #