MySQL Performance Tip - Indexes and Wildcards

This one is kind of obvious once you think about it, but I had overlooked it in my code, so I figured maybe some other webmasters out there may have as well.  I found this snippet explaining the tip I want to share from a site called websitedatabases.com:

 MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered.

The following SELECT statements will not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE other_col;

For me, some of my most visited pages had some hidden queries buried deep in includes that were using front-end wildcards to search some of my largest tables.  It came to my attention the other day when I had to restart my server during the busiest time of my website's day (around 2-3pm).  My server could not catch up with the traffic due to the heavy pounding on my database.  

Typically it is not an issue since I use memcached to limit the usage of my database.  But in this case, my cache was lost due to the reboot, and every request was hammering the database.  So it forced me to take a closer look at my code.  Low and behold I found a number of these front-end wildcards in queries that I thought were using my indexes.   The funny part was that I really didn't need the wildcard on the front-end of the string pattern.  I must have just added it to try and get more results.  

I quickly fixed my code/queries and the site bounced back to its typical performance levels... and the database let out a sigh of relief... ;-)