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

[Jan Janak]

On 26-11-2005 17:17, Juha Heinanen wrote:
> 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,
>  > 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?

  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.