Improve performance on a MySQL full text search query
I have the following MySQL query:
SELECT p.*, MATCH (p.description) AGAINST ('random text that you can use in sample web pages or typography samples') AS score
FROM posts p
WHERE p.post_id <> 23
AND MATCH (p.description) AGAINST ('random text that you can use in sample web pages or typography samples') > 0
ORDER BY score DESC LIMIT 1
With 108,000 lines, it takes ~ 200ms . With 265,000 lines, it takes ~ 500ms .
In performance testing (~ 80 concurrent users) it shows an average latency of ~ 18 sec .
Can performance improve for this query?
EXPLAIN OUTPUT:
UPDATED
We have added a new MyISAM table to mirror from post_id
, description
and synchronized it with the table posts
through triggers. Now full text search on this new MyISAM table runs for ~ 400ms (with the same load where InnoDB shows ~ 18s ). That's a huge performance boost. See how MyISAM is much faster for full-text in MySQL than InnoDB. Could you explain this?
MySQL profiling results:
Tested on AWS RDS db.t2.small instance
Original InnoDB table posts
:
MyISAM mirror table with post_id, description only:
source to share
Here are some tips for what to look for in order to maximize the speed of such queries with InnoDB:
Avoid oversorting. As InnoDB has already sorted the result according to the ranking. The MySQL query processing level does not need to be sorted to get the best results.
Avoid sampling by row to get a matching score. InnoDB provides all the relevant entries. All those who are not in the list of results should have a rating of 0, and they do not need to be searched. And InnoDB has a total of matching records. No need to recount.
Covered index scan. InnoDB results always contain the matching document ID and their rating. Therefore, if only the document ID and ranking is required, there is no need to go to the users table to get the record itself.
Refine your search results early, reduce access to the custom table. If the user wants to get the top N matching records, we don't need to grab all matching records from the user's table. We can first select the TOP N matching DOC IDs and then select only matching records with those Doc IDs.
I dont think you cant get much faster by looking only at the query itself, perhaps try to remove the part ORDER BY
to avoid unnecessary sorting. To go deeper into this, perhaps profile your query using the MySQL inbuild profiler .
In addition, you can view your MySQL server configuration. Check out this chapter of the MySQL manual for some useful information on how to customize the full-text pointer to your needs.
If you've already maximized the configuration options for your MySQL server, consider the hardware itself - sometimes even with a lost solution like moving tables to another, a faster hard drive can work wonders.
source to share
It's too long for a comment.
My best guess for the performance hit is the number of rows returned by the query. To test this, just uninstall order by score
and see if it improves performance.
If it is not, then the problem is the full text index. If so, then the problem is order by
. If so, the problem becomes a little more complicated. Some ideas:
- Determine a hardware solution to speed up sorting (getting intermediate files in memory).
- Modifying the query to return fewer values. This could include changing the stopword list, changing the query to boolean mode, or other ideas.
- Find another way to pre-filter the results.
source to share
The problem is here WHERE p.post_id <> 23
Design your system so that non-indexed columns are not added to the WHERE clause. Basically MySQL will look for the full text indexed column and then filter the post_id. Therefore, if full-text search returns many matches, response times will not be as expected.
source to share