2011/6/6 David Villasmil <david.villasmil.work(a)gmail.com>om>:
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.
--
Iñaki Baz Castillo
<ibc(a)aliax.net>