The query runs correctly, but I'm looking for a way to optimize the query below.
There is a table called services with approximately 3 million rows.
Assuming there is a column called name, I want to search for a substring using the name column.
Since I am not sure about the string length of the name column, I set it to text type.
Because the result may have several hundred thousand rows or several million rows, the limit offset syntax was used.
First, I created an index with the name column set to FULLTEXT.
The query is as follows and statement is very simple. (There are actually other operations, but the query execution time mentioned here is where all the problems start.)
SELECT *
FROM services
WHERE name LIKE '%word1%' AND name LIKE '%word2%'
LIMIT 5000 OFFSET 0
(At this time, let’s say the search results for word1 and word2 are a total of 200,000 rows.)
When experimenting with the above query, if I run this query with word1 and word2, it takes about 0.3 seconds, if I give OFFSET 5000 for 2-page results, it takes 1.5s, and if I give 10,000, it takes about 3 seconds. This is fine up to this point.
However, if I run this query with word3 and word4, the execution time varies greatly. (Let’s say the search results for word3 and word4 total 20,000 rows)
SELECT *
FROM services
WHERE name
LIKE '%word3%' AND name LIKE '%word4%'
LIMIT 5000 OFFSET 0
The above query execution time takes approximately 4 seconds, if I set the offset to 5,000 for 2-page results, it will take 18 seconds, and if I set the offset value to 10,000 for 3-page results, it will take more than 60 seconds.
I understand that the query execution time can increase depending on the OFFSET value, but I do not understand why the execution time varies greatly depending on which search words are used (which actually seems to be related to the number of records in the total search results).
How should I create indexes and queries to get similar execution times no matter and run it faster as I possible what search term I use?
Additionally, I can use 3, 5, or 10 words as a search term. If I use more than 3 words, the execution time increases significantly compared to searching with 2 words.
Do you have any ideas on how to optimize this query?