26 Nov
2005
26 Nov
'05
12:04 p.m.
On 26-11-2005 17:17, Juha Heinanen wrote:
Stefan Prelle writes:
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.
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?