On 26-11-2005 17:17, Juha Heinanen wrote:
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,
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?
Yes, it is much slower than exact match, because mysql cannot make use of indexes in this case. You can prefix the query with "explain", this will tell you how exactly is the query planned and executed. If there is no index that can be used then the query would often result in full table scan.
Jan.