Why mysql fulltext-search so slowly?

Question

The table xx_user_detail, user_id is int primary index, site1-site9 is combined fulltext index, the total row count is 5 million

SELECT * FROM xx_user_detail WHERE (user_id=14) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9) AGAINST ('苏娟的食品店');

this SQL1 use 0.3s

SELECT * FROM xx_user_detail WHERE (user_id=14 or user_id=15) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9) AGAINST ('苏娟的食品店');

this SQL2 use 2.5s

1 Why SQL2 so slowly than SQL1 by just add one row? How to improve SQL2?
2 The SQL1, use 0.3s for just scan one row, is it normal? If not, how to improve it?

the table structure is:

CREATE TABLE xx_user_detail (
  user_id int(11) NOT NULL,
  site1 varchar(20) NOT NULL,
  site2 varchar(20) NOT NULL,
  site3 varchar(20) NOT NULL,
  site4 varchar(20) NOT NULL,
  site5 varchar(20) NOT NULL,
  site6 varchar(20) NOT NULL,
  site7 varchar(20) NOT NULL,
  site8 varchar(20) NOT NULL,
  site9 varchar(20) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

ALTER TABLE xx_user_detail ADD FULLTEXT INDEX the_site_index(site1,site2,site3,site4,site5,site6,site7,site8,site9) WITH PARSER ngram;

Have you tried the inline select for the table part? SELECT * FROM (SELECT * FROM xx_user_detail WHERE (user_id=14 or user_id=15)) as tbl where MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9) AGAINST ('苏娟的食品店'); — Arash Mohammadi
– Arash Mohammadi, Commented Nov 25, 2024 at 7:05
I don't think this question has anything to do with fulltext search. Btw, looking at your table structure, I'm not even sure you need a fulltext search here - you seem to be looking at exact matches against multiple columns in a table that is not normalised properly. — Shadow
– Shadow, Commented Nov 25, 2024 at 10:39
Please post the execution plan. Why MyISAM, I suggest switching to InnoDB — Ergest Basha
– Ergest Basha, Commented Nov 25, 2024 at 15:42

O. Jones · Accepted Answer · 2024-11-25 02:05:06Z

1

The OR statement forces the databas to search the whole table

so

SELECT * FROM xx_user_detail WHERE  (user_id=14 ) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9)  AGAINST ('苏娟的食品店');
UNION
SELECT * FROM xx_user_detail WHERE  (user_id=15 ) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9)  AGAINST ('苏娟的食品店');

Will lead to faster results

edited Nov 25, 2024 at 2:05

O. Jones

110k17 gold badges134 silver badges187 bronze badges

answered Nov 25, 2024 at 2:00

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

scvyao Nov 25, 2024 at 3:15

thanks, this can improve it, but if the user_id in 14,15,16,17...., use union is still slowly

Shadow Nov 25, 2024 at 11:40

Why not just simply use the in operator instead of unions?

scvyao Nov 28, 2024 at 8:32

thx, user 'in' can improve it in my test

Rick James May 7 at 6:41

IN has been improved in InnoDB, but I suspect not in MyISAM.

zb cxy · Accepted Answer · 2024-11-27 07:06:23Z

The first query statement took 0.3 seconds. It mainly depends on the size of each row of data and the performance of your disk. If your disk performance is average and each row of data is large, then 0.3 seconds may be a normal value. I have checked the execution plans for your two statements. The first statement used the Primary Key. After matching to a specific row of data, it directly searched for the keyword "苏娟的食品店" within this row of data. It didn't use the full-text index, so compared to the second statement, its performance will be better.

mysql>
mysql> explain SELECT * FROM xx_user_detail WHERE  (user_id=14) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9)  AGAINST ('苏娟的食品店');
+----+-------------+----------------+------------+-------+------------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type  | possible_keys          | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+-------+------------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | xx_user_detail | NULL       | const | PRIMARY,the_site_index | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+----------------+------------+-------+------------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> explain SELECT * FROM xx_user_detail WHERE  (user_id=14 or user_id=15) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9)  AGAINST ('苏娟的食品店');
+----+-------------+----------------+------------+----------+------------------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type     | possible_keys          | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+----------+------------------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | xx_user_detail | NULL       | fulltext | PRIMARY,the_site_index | the_site_index | 0       | const |    1 |   100.00 | Using where |
+----+-------------+----------------+------------+----------+------------------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

The second statement, as seen from the execution plan, uses a full - text index. Given the large amount of data in the table, 2.5 (seconds) might be a normal value. You can optimize it through the following two methods:

Replace "OR" with "IN", In many database servers, IN() is just a synonym for multiple OR clauses, because the two are logically equivalent. Not so in MySQL, which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list. This is O(Log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists)
Replace "OR" with "UNION ALL", so that it can use the Primary Index like the first statement.

Since there was no data volume when I was doing the testing, the execution plan might be different from yours. You could post your execution plan and the cost time after optimization.

Collectives™ on Stack Overflow

Why mysql fulltext-search so slowly?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related