CODEWITHSUNDEEP
mysqlquery-optimizationdatabase-indexes
YD8877
YD8877

Reputation: 10780

Is there a difference between columnname like '%%' and no condition at all?

Is there a difference in these 2 queries (optimization wise) ?

select * from users;

and

select * from users where first_name like '%%' and last_name like '%%'

I am building the query in PHP dynamically using the parameters passed. So, for example..

$first_name_str = "";
if($firstname)
{
    $first_name_str = "first_name = '%".$firstname."%' and";
}

$last_name_str = "";
if($lastname)
{
    $last_name_str = "last_name = '%".$lastname."%' and";
}


$query = 
"select
        *

from    
    users

where
    ".$first_name_str."
    ".$last_name_str."
    1=1";

The reason i am asking this is because i read that mysql uses only one index while doing a select. So, if i have individual indexes on firstname and lastname, only one will be used. In the case that i have the query as :

select * from users where first_name like '%%' and last_name like '%%'

by default, i can add a concatenated index on both first_name and last_name and searching will be much faster ?

Upvotes: 0

Views: 233

Answers (3)

Patrick Echterbruch
Patrick Echterbruch

Reputation: 679

//EDIT: Did read the first line of your question to late, so I missed the "optimization wise" part... Now my answer is a bit off topic, but not entirely wrong, so I'm not going to delete it. Maybe someone finds it useful anyway...

Many things about indexes have already been said, so I have nothing to add.

But there's another important point that may or may not come in your way, depends on your table setup:

LIKE comparison to NULL always yields NULL, so if your table has rows in which last_name or first_name is NULL, then WHERE <field> LIKE '%' (or '%%' or '%%%') will not return this row (because NULL LIKE '%' returns NULL which is obviously not TRUE).

Upvotes: 0

Zimbabao
Zimbabao

Reputation: 8240

Like '%' is same as Like '%%' or Like '%%%' or LIKE '%%%%'.

To check this yourself just run explain on the query. Se some example queries I ran on my table.

mysql> explain select * from USERS where EMAIL like '%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | USERS | ALL  | NULL          | NULL | NULL    | NULL |  415 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)

mysql> explain select * from USERS where EMAIL like '%%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | USERS | ALL  | NULL          | NULL | NULL    | NULL |  415 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from USERS where EMAIL like '%%%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | USERS | ALL  | NULL          | NULL | NULL    | NULL |  415 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

2 points of @Iain are right way to go about performance. But try to locate most of the performance problems in staging using load testing.

Upvotes: 2

Iain Ballard
Iain Ballard

Reputation: 4820

Most SQL servers (and I think MySql is one of them) do their best to use indexes well with the LIKE keyword.

Using LIKE '%' should be as fast as no condition for most queries. I'm not sure about LIKE '%%'

But generally there are two important things to remember when it comes to performance optimisation:

  1. Don't worry unless it is a problem
  2. If it needs optimising, measure it (tracing tools, profilers etc.)

Upvotes: 0

Related Questions