Slow MySQL query using WHERE like

Question

This is the query I'm using to perform a forum search.

$sql = "SELECT t.title, t.user_id, t.replies, t.views, t.last_poster_name, t.last_post_time, a.username
FROM fm_topics t 
LEFT JOIN fm_replies r 
ON t.t_id=r.topic_id 
LEFT JOIN account a 
ON t.user_id=a.id
WHERE 
`title` LIKE '%" . sanitize($search_string) . "%' OR
`content` LIKE '%" . sanitize($search_string) . "%'
GROUP BY t_id LIMIT 0, 10";

The above works fine. However, it takes like 5+ seconds to load. Now, if I take out the part where it searches for the title too, it loads way faster (obviously). My question is, how can I improve this query? how would you rewrite it for better performance?

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

You can use MySql functions like MATCH and AGAINST. By default, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

Natural Language Full-Text Searches

The query needs no optimization instead the table might if indexing is not done on that table.

What is an index in SQL Server?

And for your scenario, i.e. text searching using wildcard, Full text indexing is recommended.

When should you use full-text indexing?

edited May 23 '17 at 11:49

Community

1
1

answered Feb 07 '14 at 22:10

hsuk

6,770
13
50
80

What? Question is asked for `MySql` and not `Sql Server`. – Rahul Feb 07 '14 at 22:13
The link is about concept. Its not about syntax and all. – hsuk Feb 07 '14 at 22:14
True but user may confuse it. So, should be redirected to proper place like indexing in mysql dev document. – Rahul Feb 07 '14 at 22:16

score 0 · Accepted Answer · answered Feb 07 '14 at 22:10

Why not make a full-text search and index it? That will make searches much faster. By using a LIKE query, especially one that is prefix wild-carded it means every row has to be sequentially scanned.

https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

I would make two-column full-text index on title,content:

CREATE FULLTEXT INDEX index_content ON (title,content)

Than search on it like:

SELECT ... WHERE MATCH(title, content) AGAINST ("your query")

Optionally using IN BOOLEAN MODE - read the docs for reasons on why you would and would not use this modifier.

If you really cannot implement a full-text search+index and need to keep the LIKE then you can consider just adding a regular index on content, but then to take advantage of that index you will have to remove the leading wild-card (you can keep the trailing one); MySQL cannot use an index on a leading-wildcard query.

Slow MySQL query using WHERE like

2 Answers2