MySQL Query Optimization - Tip # 1 - Avoid using wildcard character at the start of a LIKE pattern.

The more I go through others SQL, there are some common mistakes that I see developers making over and over again, so I thought why not start a series of tips that can help developers optimize their queries and avoid common pitfalls.

So this post is a part of that series of tips, and this is the first tip to start things off!

Avoid using a wild card character at the start of a LIKE pattern

There are many times I have seen SQL containing the following:

SELECT * FROM tbl WHERE col1 LIKE '%x%';

This is something that should be avoided because MySQL would not use an index for such a query.

Here is a relevant quote from MySQL Internals Manual:
“The optimizer will use an index (range search) for
column1 LIKE ’x%’
but not for
column1 LIKE ’%x’
That is, there is no range search if the first character in the pattern is a wildcard.”

Let’s test this out using some sample data.

/*Table structure for table `scores` */

CREATE TABLE `scores` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `score` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `scores` */

insert  into `scores`(`id`,`name`,`score`) values (1,'ovais',99),(2,'umair',100),(3,'hassan',78),(4,'saleem',66),(5,'zeeshan',32);

Query with the first character as a wild card character

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

Query with the first character as a non-wild card character

mysql> explain select * from scores where `name` like 'o%';
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | scores | range | student_name  | student_name | 258     | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Conclusion

Take a look at the explain output of the first query, especially the type column that says 'ALL', what that means that the index on the name column student_name cannot be used, instead MySQL does a “sequential scan of table” commonly referred to as table scan which is a very costly operation.

Now take a look at the explain output of the second query, its much better than the first one, the type column now says 'range', which means MySQL would do a range scan on the key ‘student_name’ for all names starting with ‘o’.