2011/6/6 David Villasmil david.villasmil.work@gmail.com:
I had much the same traffic, and with a good index that wouldn't be a problem, IMHO.
Hi David, your query cannot make usage of table indexes:
select * from routes where '$rU' like concat(areacode,'%') order by len(areacode) desc limit 1;
You are using "LIKE" and concat(TABLE_COLUMN...) so you are forcing the database server to read *all* the values of "areacode" (all the rows), create a new string by concatening "%" at the end, and then performing a regular expressión (LIKE "XXX%") for *every* resulting values. This can never use an index.
You can check it by yourself by entering into mysql console:
DESCRIBE select * from routes where '999999999' like concat(areacode,'%') order by len(areacode) desc limit 1;
You will realize that no index is being used.
You could also load the table in memory, can't be faster than that.
Yes, but for that a custom module is required :)
Of course, doing it in the config script is faster, but it limits your flexibility... just a thought...
Humm, in fact not. I can manage the table content via a web interface (or whatever) and running a MI "reload" command for the used module so the table content is read again into memory. This is the same as I do with LCR module and others.
Regards.