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

• Related Questions & Answers
• Append wildcards in SELECT with MySQL?
• Improve performance of a HTML5 Canvas with particles bouncing around
• How to improve the performance of JavaScript?
• How to improve file reading performance in Python with MMAP function?
• How does pipelining improve performance in computer architecture?
• Wildcards in column name for MySQL?
• What are the best practices to improve jQuery selector performance?
• How to improve the performance of my Python CGI script?
• How can I improve performance of a Laptop or PC?
• mysql_secure_installation - Improve MySQL Installation Security
• How to improve the ranking of your websites for search engines
• MySQL query to search record with apostrophe?
• MySQL query for alphabetical search (ABC) with REGEXP?
• Search for specific characters within a string with MySQL?
• How to search specific word in MySQL with RegExp?