Full-text "title" search in the 1st row

Question

There is a table of 3.3GB articles Myisam with the following fields: id, title, perma, body, date primary key: id full-text index: title

It has 1,110,000 lines. After this I:

 SET GLOBAL key_buffer_size = 2000*1024*1024; LOAD INDEX INTO CACHE articles INDEX(title); 

I can’t get enough performance.

You can see the runtime of several samples below:

 <9.5381848812103> SELECT SQL_NO_CACHE perma,title,body, MATCH(title) AGAINST('flowers for children' IN BOOLEAN MODE) AS sort FROM articles WHERE MATCH(title) AGAINST('flowers for children' IN BOOLEAN MODE) ORDER BY sort DESC LIMIT 30; <12.734259843826> SELECT SQL_NO_CACHE perma,title,body, MATCH(title) AGAINST('how to play basketball' IN BOOLEAN MODE) AS sort FROM articles WHERE MATCH(title) AGAINST('how to play basketball' IN BOOLEAN MODE) ORDER BY sort DESC LIMIT 30; <4.4655818939209> SELECT SQL_NO_CACHE perma,title,body, MATCH(title) AGAINST('kill a bird and eat it' IN BOOLEAN MODE) AS sort FROM articles WHERE MATCH(title) AGAINST('kill a bird and eat it' IN BOOLEAN MODE) ORDER BY sort DESC LIMIT 30; <16.268588066101> SELECT SQL_NO_CACHE perma,title,body, MATCH(title) AGAINST('avoid back pain' IN BOOLEAN MODE) AS sort FROM articles WHERE MATCH(title) AGAINST('avoid back pain' IN BOOLEAN MODE) ORDER BY sort DESC LIMIT 30; <12.553371906281> SELECT SQL_NO_CACHE perma,title,body, MATCH(title) AGAINST('computer' IN BOOLEAN MODE) AS sort FROM articles WHERE MATCH(title) AGAINST('computer' IN BOOLEAN MODE) ORDER BY sort DESC LIMIT 30; 

Any suggestions to improve the lead time?

Answer 1

Here is your first request

 SELECT SQL_NO_CACHE perma,title,body, MATCH(title) AGAINST('flowers for children' IN BOOLEAN MODE) AS sort FROM articles WHERE MATCH(title) AGAINST('flowers for children' IN BOOLEAN MODE) ORDER BY sort DESC LIMIT 30; 

You may need to reorganize this

First select the keys and sort value

 SELECT id,MATCH(title) AGAINST ('flowers for children' IN BOOLEAN MODE) sort FROM articles WHERE MATCH(title) AGAINST ('flowers for children' IN BOOLEAN MODE); 

This query will result in a 800M pace table

Next, limit it to the 30 highest sort values

 SELECT * FROM ( SELECT id,MATCH(title) AGAINST ('flowers for children' IN BOOLEAN MODE) sort FROM articles WHERE MATCH(title) AGAINST ('flowers for children' IN BOOLEAN MODE) ) AA ORDER BY sort DESC LIMIT 30; 

Ok now the temporary table is 720 bytes

Finally, LEFT JOIN these 30 rows into the articles table

 SELECT B.perma,B.title,B.body,A.sort FROM ( SELECT * FROM ( SELECT id,MATCH(title) AGAINST ('flowers for children' IN BOOLEAN MODE) sort FROM articles WHERE MATCH(title) AGAINST ('flowers for children' IN BOOLEAN MODE) ) AA ORDER BY sort DESC LIMIT 30 ) A LEFT JOIN articles B USING (id); 

Give it a try !!!

Answer 2

Try using the default "IN NORMAL MODE" rather than "IN BOOLEAN MODE".

I'm not sure if there is any special reason why you chose "IN BOOLEAN MODE". I noticed that you have not used any of the operators that "IN BOOLEAN MODE" offers. In addition, due to the large data set, you do not have to worry about the 50% threshold.

I suggest this because "IN NORMAL MODE" also sorts the results by relevance without the need for "ORDER BY". This is consistent with the MySQL documentation.

Oh, and if you have not changed the minimum word size, the shortest words included in "FULLTEXT INDEX" will contain 4 letters. Thus, the “for” in your “AGAINST (...)” would be superfluous if the size of the minimum word had not changed.