mysql: double condition WHERE LIKE slows down SELECT query

Question

I've 2 tables:

POSTS (about 2000 rows)
ID  title

and

ELEMENTS (about 60000 rows)
postID (fK)    elementfield

Basically I would like to return the ID if the searchterm is in posts.title or in elements.elementname. I use the following query:

SELECT SQL_CALC_FOUND_ROWS posts.ID 
FROM posts 
LEFT JOIN elements 
ON posts.ID = elements.postID 
WHERE (
  posts.post_title LIKE '%searchterm%' 
  OR elements.elementfield LIKE '%searchterm%' 
  )

It works, but it's very very slow (about 40 seconds!).

I believed it was the size of the elements table (over 60.000 rows) but if I run the same query with only one WHERE clause

WHERE  elements.elementfield LIKE '%searchterm%'

it returns the find in a fraction of second...

I'm not a DB expert, but this looks very basic... What am I doing wrong? Thanks for your help!

EDIT @Solarflare Run EXPLAIN I have something like this:

id     select_type     table     type    possible_keys     rows      Extra
1      SIMPLE          posts     eq_ref  PRIMARY,type_     1         Using 
                                         status_date                 Where 

1      SIMPLE          elements  ALL     NULL              67774   Using     
                                                                    where; 
                                                                    Using 
                                                                    join 
                                                                    buffer 
                                                       (Block Nested Loop)

does `elements.elementfield` have a fulltext index? even if its not on the entire length it should help a great deal — csminb, Jan 06 '17 at 18:24
A full table scan of 60000 + 2000 rows should not take 40 seconds. Can you add the explain output for your query (write `explain ` directly infront of your query and post the result)? You are maybe missing an index on `elements.postID`. — Solarflare, Jan 06 '17 at 20:59

Charles Bretana · Accepted Answer · 2017-01-06T18:35:09.777

1

using Or in where clauses is notoriously non-performant. try this:

SELECT SQL_CALC_FOUND_ROWS p.ID 
FROM posts p
  LEFT JOIN elements e
    ON p.ID = e.postID 
WHERE CONCAT(p.post_title, e.elementfield) LIKE '%searchterm%'

by the way, what is SQL_CALC_FOUND_ROWS, a built-in function?

edited Jan 06 '17 at 18:35

answered Jan 06 '17 at 18:14

Charles Bretana

143,358
22
150
216

`+` is a string concatenation operator in Microsoft SQL Server and Microsoft Access, but not in MySQL. – Bill Karwin Jan 06 '17 at 18:24
[`SQL_CALC_FOUND_ROWS`](http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows) is a query modifier that causes the number of matching rows to be remembered, so if you run a query with `LIMIT` (similar to `TOP` in Microsoft), you can call a function to find out how many total rows there would have been if you hadn't used `LIMIT`. In this query example, `SQL_CALC_FOUND_ROWS` is nearly redundant, because the query has no `LIMIT` clause. – Bill Karwin Jan 06 '17 at 18:25
Thanks, it's fast, but returns nothing... any other suggestion? PS yes `SQL_CALC_FOUND_ROWS` is in wordpress – codeispoetry Jan 06 '17 at 18:34
Changed the `+` to use the mySQL string concatenation function. Did you try it with that change? – Charles Bretana Jan 06 '17 at 18:35
@Charles Thanks Charles. Tried in phpMyadmin and it works very fast. I tested also with the `SQL_NO_CACHE` as per @Bill suggestion. My last problem is that now I need to implement it in Wordpress, because it adds another `OR (posts.post_content LIKE '%searchterm%')` and I don't know how to filter it out from the query. – codeispoetry Jan 06 '17 at 20:43
@codeispoetry, I'm sorry, I don't know Wordpress... Perhaps someone else can help with that. – Charles Bretana Jan 06 '17 at 21:08

score 1 · Answer 2 · edited May 23 '17 at 12:09

it returns the find in a fraction of second...

This is fast probably thanks to the MySQL query cache. You'll find it runs slow again if you run the query with the option to skip the query cache:

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS posts.ID 
FROM posts 
LEFT JOIN elements 
ON posts.ID = elements.postID 
WHERE elements.elementfield LIKE '%searchterm%'

Any query with LIKE '%pattern%' has to do a table-scan. That predicate cannot use an index.

See my presentation Full Text Search Throwdown.

You may be able to create a FULLTEXT INDEX on each table and use the MATCH() function.

But you can't create a single index that spans multiple tables (see my answer to mySQL MATCH across multiple tables).

You mentioned in a comment:

SQL_CALC_FOUND_ROWS is in wordpress

Does this mean you have no opportunity to change the SQL query (besides changing Wordpress code)? If so, then you're out of luck. File a bug fix request with Wordpress.

In some cases, SQL_CALC_FOUND_ROWS query modifier has a huge performance penalty on queries. Depends on the size of the data set and whether you have indexes to help the query. But you should test your query both with and without SQL_CALC_FOUND_ROWS and see which is faster.

Thanks Bill, very helpful. But I tested Charles solution and it works. Pls, see my comment at his answer. — codeispoetry, Jan 06 '17 at 20:45

mysql: double condition WHERE LIKE slows down SELECT query

2 Answers2