Improve MySQL Search Performance with wildcards (%%)?

No, MySQL won’t improve search performance whenever you have leading wildcards because MySQL will be unable to use the index. If you change to ‘anyLetter%’ then it will be able to use index

The below syntax is better to use with trailing wildcards. The syntax is as follows −

SELECT *FROM yourTableName WHERE yoorColumnName LIKE ‘anyLetter%’;

The query to create a table is as follows −

mysql> create table TrailingWildCardDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name Varchar(20),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.71 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into TrailingWildCardDemo(Name) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into TrailingWildCardDemo(Name) values('Johnson');
Query OK, 1 row affected (0.17 sec)
mysql> insert into TrailingWildCardDemo(Name) values('Mike');
Query OK, 1 row affected (0.16 sec)
mysql> insert into TrailingWildCardDemo(Name) values('Kevin');
Query OK, 1 row affected (0.23 sec)
mysql> insert into TrailingWildCardDemo(Name) values('Adam');
Query OK, 1 row affected (0.20 sec)
mysql> insert into TrailingWildCardDemo(Name) values('Ethan');
Query OK, 1 row affected (0.16 sec)
mysql> insert into TrailingWildCardDemo(Name) values('James');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from TrailingWildCardDemo;

The following is the output −

+----+---------+
| Id | Name    |
+----+---------+
|  1 | John    |
|  2 | Johnson |
|  3 | Mike    |
|  4 | Kevin   |
|  5 | Adam    |
|  6 | Ethan   |
|  7 | James   |
+----+---------+
7 rows in set (0.00 sec)

Here is the query of trailing wildcards −

mysql> select *from TrailingWildCardDemo where Name LIKE 'j%';

The following is the output −

+----+---------+
| Id | Name    |
+----+---------+
|  1 | John    |
|  2 | Johnson |
|  7 | James   |
+----+---------+
3 rows in set (0.00 sec)

Karthikeya Boyini

Published on 26-Feb-2019 10:43:38

Previous Page Print Page