[SR-Users] Using =~ to get the group identifier with longest match

[Iñaki Baz Castillo]

2011/6/6 David Villasmil <david.villasmil.work at 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.



-- 
Iñaki Baz Castillo
<ibc at aliax.net>