MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing queries with Wildcards
Posted by: George K
Date: November 19, 2010 08:54AM

Hello Everyone,
Recently I had a pleasure to assist migration of a custom PHP solution for a "Directory" type website to a VPS host, which resulted in having abnormal server loads.
We have analyzed the situation and found out that the Load was caused by MySQL.
The database itself is quite small, only ~30 000 records in total and 15MB in size and I was quite surprised by the magnitude of the loads...
Further analysis showed that their queries mostly use Joins within 4 tables and use Wildcards everywhere.

An example of one of their queries is something like this:

select distinct id from table1, table2, table3, table4 where (table4.id = 1 ) and (table1.column1 like '%string%' or table1.column2 like '%string%' or table1.column3 like '%string%' or table1.column4 like '%string%' or table1.column5 like '%string%' or table1.column6 like '%string%' or table2.column1 like '%string%' or table2.column2 like '%string%' or table2.column3 like '%string%' ) and table1.id=table2.xid and table2.xid=table3.id and table1.id=table4.id;

Now I am aware that the query is far from perfect, but the problem is that it cannot be changed and thus if there is any chance of optimization it shall be done within a table (column types, indexes etc) or database configuration (cache etc).

Database Configuration was changed so that as much RAM was allocated to various caching types as the system could spare. The results were reassuring but not quite enough.

The analysis of Table structure and queries with "explain" showed that 3 columns present in the query were not indexed at all (which we have quickly fixed). However it didn't had any effect...

What I concluded after some more research was that the query is using wildcards in the beginning and at the end of the string and thus the query cannot utilize indexes properly.
As I cannot change query structure I cannot use Full-Text indexing as well... (am I right?)

I'd say my hands are tied, but I may have overlooked some aspect or made incorrect conclusions...
So I'm asking an advice from more experienced people in here - Is there any other field I have missed during my research that would help optimize the performance ?

I would be thankful if you could give some direction on where to research more.
Thank you in advance,
George

Edit: I forgot to mention that we are running under MySQL 5.0 branch.



Edited 1 time(s). Last edit at 11/19/2010 08:59AM by George K.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing queries with Wildcards
3580
November 19, 2010 08:54AM
1215
November 20, 2010 11:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.