MySQL Like function

In general, the LIKE function will be slow unless it begins with a fixed string and the column has an index. If you do LIKE 'foo%', it can use the index to find all rows that begin with foo, because MySQL indexes use B-trees. But LIKE '%foo' cannot make use of an index, because B-trees only optimize looking for prefixes; this has to do a sequential scan of the entire table.

And even when you use the version with a prefix, the performance improvement depends on how much that prefix reduces the number of rows that have to be searched. If you do LIKE 'foo%bar', and 90% of your rows begin with foo, this will still have to scan 90% of the table to test whether they end with bar.

Since LIKE '%%' doesn't have a fixed prefix, it will perform a full scan of the table, even though there isn't actually anything to search for. It would be best if your PHP script tested whether the user provided a search string, and omit the LIKE test if there's nothing to search for.

I believe the LIKE function can slow the query down a lot

I would expect that not to be the case. How hard would it be to test it?

Regardless which version of the query you run, the DBMS still has to examine every row in the table. That will require some extra work by the CPU, but for large tables, disk I/O will be the limiting factor. LIKE '%%' will discard rows with null values - hence potentially reducing the amount of data the DBMS needs to retain in the result set / transfer to the client which may be significant saving.

As Barbar says, providing an expression without a leading wildcard will allow the DBMS to use an index (if one is available) which will have a big impact on performance.

Its hard to tell from your question (you didn't provide much in the way of example queries/data nor any detail of what the application does) but the solution to your problem might be full text indexing

Using the World database sample from the mysql software distribution, I first did a simple explain on queries with and without where clauses without filtering effects:

mysql> explain select * from City;

mysql> explain select * from City where true;

mysql> explain select * from City where Name = Name;

In these first three cases, the result is as follow:

+----+--------------+-------+------+----------------+-----+---------+-----+------+-------+
 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------+------+----------------+-----+---------+-----+------+-------+
|  1 | SIMPLE      | City  | ALL   | NULL          | NULL | NULL    | NULL | 4080 |       |
+----+--------------+-------+------+----------------+-----+---------+-----+------+-------+

While for the last query, I got the following:

mysql> explain select * from City where Name like "%%";

+----+--------------+-------+------+----------------+-----+---------+-----+------+-------+
 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra       |
+----+--------------+-------+------+----------------+-----+---------+-----+------+-------+
|  1 | SIMPLE      | City  | ALL   | NULL          | NULL | NULL    | NULL | 4080 | Using where |
+----+--------------+-------+------+----------------+-----+---------+-----+------+-------+

You can see that for this particular query, the where condition was not optimized away.

I also performed a couple of measurements, to check if indeed there would be a sensible difference, but:

• the table having only 4080 rows, I used a self cross join to render longer computation times

• I used having clauses to cut down on display overhead ⁽¹⁾.

Measurement results:

mysql> select c1.Name, c2.Name from City c1, City c2 where concat(c1.Name,c2.Name) = concat(c1.Name,c2.Name) having c1.Name = ""; 
Empty set (5.22 sec)

The above query, as well as one with true or c1.Name = c1.Name performed sensibly the same, within less than a 0.1 sec margin.

mysql> reset query cache;

mysql> select c1.Name, c2.Name from City c1, City c2 where concat(c1.Name,c2.Name) like "%%" having c1.Name = "";
Empty set (13.80 sec)

This one also took around the same amount of time when run several times (in between query cache resets) ⁽²⁾.

Clearly the query optimizer doesn't see an opportunity for the later case. The conclusion is that you should try to avoid as much as possible the use of that clause, even if it doesn't change the result set.

(1): having clause filtering happening after data consolidation from the query, I assumed it shouldn't change the actual query computation load ratio.

(2): interestingly, I initially tried a simple where c1.Name like ”%%", and got around 5.0 sec. timing results, which led me to try out with a more elaborate clause. I don't think that result changes the overall conclusion; it could be that in that very specific case, the filtering actually has a beneficial effect. Hopefully a mysql guru will explain that result.