Stefan Prelle writes:
- Then I inserted 1 Million routes into the "lcr" table, which resulted in a query time of 10 seconds. So I created an index for lcr.prefix - reducing the processing time to 1 seconds.
stefan,
one million "routes" in lcr table is a very big number. when i wrote lcr module, i had in mind usage where lcr table would hold a rather small number of prefixes, such as for different countries and area codes. i never thought that the table would hold individual phone numbers.
Since 1 second per Call is way too much to achieve the targeted 200+ CPS, I changed the SQL string in the LCR module from a LIKE comparision to an exact match comparison. The result was very satisfying (MySQL CLI said 0.00 seconds per request)
The lcr table will contain prefixes, so I needed a mechanism to perform multiple exact match requests, while shortening the dialed number to compare. Because my C/C++ is basically just enough for "Hello World", I switched to MySQL 5 and used stored procedures. The stored procedure I used is attached (proc.sql). I changed the SQL query to use this procedure.
are you saying that LIKE operation is much slower that a many exact matches? if so, it looks like a bug in mysql's LIKE implementation. have you asked mysql list, why LIKE takes so long?
-- juha