FULLTEXT поиск (тормоза)

tropico · 26 Июн 2007

FULLTEXT поиск (тормоза)

Есть такая таблица:

[sql]
CREATE TABLE `soft` (
`soft_id` char(255) collate utf8_unicode_ci NOT NULL default '',
`cat_id` varchar(30) collate utf8_unicode_ci default NULL,
`subcat_id` varchar(30) collate utf8_unicode_ci NOT NULL default '',
`title` char(255) collate utf8_unicode_ci default NULL,
`version` varchar(20) collate utf8_unicode_ci default NULL,
`pdate` date default NULL,
`description` text collate utf8_unicode_ci,
`short_description` text collate utf8_unicode_ci NOT NULL,
`big_description` mediumtext collate utf8_unicode_ci,
`keywords` char(50) collate utf8_unicode_ci default NULL,
`vendor` char(255) collate utf8_unicode_ci default NULL,
`vendor_id` varchar(50) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`soft_id`),
KEY `title` (`title`),
KEY `vendor_id` (`vendor_id`),
KEY `pdate` (`pdate`),
KEY `keywords_2` (`keywords`),
KEY `subcat_id_idx` (`subcat_id`),
FULLTEXT KEY `keywords` (`keywords`),
FULLTEXT KEY `soft_id` (`soft_id`,`title`,`description`,`short_description`,`keywords`,`vendor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
[/sql]

Есть запрос к ней вида:
[sql]
select *, MATCH (soft.soft_id, soft.title, soft.description, soft.short_description, soft.keywords, soft.vendor) AGAINST ('"test query"' ) AS score1, MATCH (soft.keywords) AGAINST ('+"test query"' ) AS score2 FROM soft where MATCH (soft.soft_id, soft.title, soft.description, soft.short_description, soft.keywords, soft.vendor) AGAINST ('"test query"' ) order by score1 DESC, score2 DESC LIMIT 10
[/sql]

+ 2 похожих, но по одному FULLTEXT индексу в условии

mysql показывает, что он использует этот индекс:

PHP:

mysql> explain select *, MATCH (soft.soft_id, soft.title, soft.description, soft.short_description, soft.keywords, soft.vendor) AGAINST ('"test query"' ) AS score1, MATCH (soft.keywords) AGAINST ('+"test query"' ) AS score2 FROM soft where MATCH (soft.soft_id, soft.title, soft.description, soft.short_description, soft.keywords, soft.vendor) AGAINST ('"test query"' ) order by score1 DESC, score2 DESC LIMIT 10;
+----+-------------+-------+----------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type     | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | soft  | fulltext | soft_id       | soft_id | 0       |      |    1 | Using where; Using filesort |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.03 sec)

однако на деле оказывается, что нет. Дикие тормоза, по gstat 100% на чтение всегда. mysql часто пребывает в состоянии biord.
В чем может быть причина?

Стоит еще сказать, что mysql сбрасывет значение cardinality по своему усмотрению

PHP:

mysql> show index from soft;
+-------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| soft  |          0 | PRIMARY        |            1 | soft_id           | A         |       44515 |     NULL | NULL   
....
| soft  |          1 | keywords       |            1 | keywords          | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |
| soft  |          1 | soft_id        |            1 | soft_id           | NULL      |           1 |     NULL | NULL   |      | FULLTEXT   |         |
| soft  |          1 | soft_id        |            2 | title             | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |
| soft  |          1 | soft_id        |            3 | description       | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |
| soft  |          1 | soft_id        |            4 | short_description | NULL      |           1 |     NULL | NULL   |      | FULLTEXT   |         |
| soft  |          1 | soft_id        |            5 | keywords          | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |
| soft  |          1 | soft_id        |            6 | vendor            | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |
...

mysqlcheck -a помогаетна 5 минут и то не всегда, потом все снова.
mysql был 4.1.18, 4.1.22, сейчас последний из 5.0 ветки. Проблема наблюдается только с этими fulltext индексами, с остальными все ок.
Еще раз повторю вопрос

В чем может быть причина?

-~{}~ 26.06.07 01:24:

Такой вот простейший тоже тормозит:
[sql]
select *, MATCH (soft.keywords) AGAINST ('+"some query"' ) AS score1, 1=1 AS score2 FROM soft where MATCH (soft.keywords) AGAINST ('+"some query"' ) order by score1 DESC, score2 DESC LIMIT 15
[/sql]

PHP:

mysql> explain select *, MATCH (soft.keywords) AGAINST ('+"some query"' ) AS score1, 1=1 AS score2 FROM soft where MATCH (soft.keywords) AGAINST ('+"some query"' ) order by score1 DESC, score2 DESC LIMIT 15;
+----+-------------+-------+----------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type     | possible_keys | key      | key_len | ref  | rows | Extra                       |
+----+-------------+-------+----------+---------------+----------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | soft  | fulltext | keywords      | keywords | 0       |      |    1 | Using where; Using filesort |
+----+-------------+-------+----------+---------------+----------+---------+------+------+-----------------------------+

по show processlist видно, что некоторые исполняются по 40-50секунд (хотя некоторые быстро пробегают)

Андрейка · 26 Июн 2007

по show processlist видно, что некоторые исполняются по 40-50секунд (хотя некоторые быстро пробегают)
некоторые это некоторые запросы в любое время или в некоторое время любые подобные запросы тормозят?

tropico · 26 Июн 2007

Таких запросов по fulltext индексу идет штук 5 в секунду. Из них часть идет секунд 5, остальные до 40-50. Тормозит в принципе ВСЕ, поскольку именно _эти_ запросы вызвывают походу полное чтение таблицы )
Страницы открываются секунд по 10 в среднем.

PHP:

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 37414
   User: ...
   Host: localhost
     db: ...
Command: Query
   Time: 131
  State: Sending data
   Info: SELECT
  soft.subcat_id,
*************************** 2. row ***************************
     Id: 37459
   User: ...
   Host: localhost
     db: ...30
Command: Query
   Time: 68
  State: Sorting result
   Info: select *, MATCH (soft.soft_id, soft.title, soft.description, soft.short_description, soft.keywords,
*************************** 3. row ***************************
     Id: 37476
   User: ...
   Host: localhost
     db: ...18
Command: Query
   Time: 47
  State: Sorting result
   Info: select *, MATCH (soft.soft_id, soft.title, soft.description, soft.short_description, soft.keywords,
*************************** 4. row ***************************
     Id: 37485
   User: ...
   Host: localhost
     db: ...2
Command: Query
   Time: 24
  State: Sorting result
   Info: select *, MATCH (soft.keywords) AGAINST ('+"..."' ) AS
*************************** 5. row ***************************
     Id: 37503
   User: ...
   Host: localhost
     db: ...3
Command: Query
   Time: 5
  State: Sorting result
   Info: SELECT * FROM
*************************** 6. row ***************************
     Id: 37506
   User: ...
   Host: localhost
     db: ...9
Command: Query
   Time: 6
  State: FULLTEXT initialization
   Info: SELECT
  soft.subcat_id,
*************************** 7. row ***************************
     Id: 37507
   User: ...
   Host: localhost
     db: ...8
Command: Query
   Time: 2
  State: FULLTEXT initialization
   Info: SELECT
  soft.subcat_id,
*************************** 8. row ***************************
     Id: 37509
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 9. row ***************************
     Id: 37512
   User: ...
   Host: localhost
     db: ...5
Command: Query
   Time: 1
  State: Sending data
   Info: SELECT
  soft.subcat_id,
*************************** 10. row ***************************
     Id: 37513
   User: ...
   Host: localhost
     db: ...18
Command: Query
   Time: 0
  State: statistics
   Info: SELECT
  soft.subcat_id,

camka · 26 Июн 2007

Сколько записей в таблице?
Попробуйте вообще убрать сортировку и сделать поиск только по одному индексу? MySQL по умолчанию сам сортирует результат по релевантности в случае поиска по полнотестовому индексу.

.des. · 26 Июн 2007

Как альтернативу можно предложить http://sphinxsearch.com
Плюс в том, что интегрировать данный продукт очень легко.

tropico · 26 Июн 2007

camka
В том то и дело, что записей всего 45000. Сортировку убрать попробую, посмотрю как себя будет вести.
Хотя раньше все и так работало как нужно

.des.
sphinx это очень круто, мне нужно что попроще

-~{}~ 26.06.07 13:59:

В общем сделал как посоветовал camka, убрал ORDER BY ..
Соответственно из explain исчез Using filesort. По gstat теперь нет 100% на чтение, slow queries перестали расти на 2000 в час.
Всем спасибо )

Wicked · 26 Июн 2007

что-то мне подсказывает, что проблема была именно из-за наличия двух параметров в ORDER BY. Не могу бы ты проверить, будет ли работать с одним:
ORDER BY score1 DESC
LIMIT 10
?

tropico · 26 Июн 2007

Wicked
в таком случае появляется Using filesort. Т.е. тормоза
Я честно говоря даже не знаю почему это так, т.к. запрос примитивный и вроде все верно

camka · 26 Июн 2007

В мануале четко написано, что сортировать не надо, иначе будет тормозить. С поиском по обоим индексам можно поэксперементировать, если надо. Если MySQL не умеет использовать оба индекса в одном запросе, используем UNION как в старые добрые времена. Если надо сортировать по общей релевантности, можно решить через временную таблицу с соответствующими индексами, куда сначала скидываются результаты поиска, а потом отдельным запросом сортируется и выбирается нужное количество записей.

FULLTEXT поиск (тормоза)

tropico

Новичок

Андрейка

Senior pomidor developer

tropico

Новичок

camka

не самка

.des.

Поставил пиво кому надо ;-)

tropico

Новичок

Wicked

Новичок

tropico

Новичок

camka

не самка