[Serusers] MySQL-Problem (was: LCR question)

[Juha Heinanen]

Stefan Prelle writes:

 > 2. 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.

 > 3. 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)
 > 
 > 4. 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