#2698 SQL parser is slow
phpMyAdmin is very slow at handling big SQL queries. Using my debugger I can see that PMA_SQP_parse() is the culprit.
A common example is a query like this:
SELECT * FROM ids WHERE id IN (123,324,543,453,234, ...
Even if the query is just a few kilobytes, the parsing takes several seconds. If the query is several hundred kilobytes, the parsing seems to take forever.
If the same query is sent to MySQL using the "mysql" command line utility, the query is executed without noticeable delay.
I don't know if it is possibly to optimize the parser enough to handle queries of almost arbitrary size. Perhaps queries longer than a certain number of characters shouldn't be parsed at all (I am not sure what consequences this has).
Using the profiler I noticed that quite a lot of overhead is added by wrapper functions like PMA_STR_isAlnum. With a few optimizations I managed to reduce the running time of PMA_SQP_parse() with about 30%. This doesn't really solve the problem, but it may be worth taking anyway.
Small optimization
Logged In: YES
user_id=210714
Originator: NO
Christian,
could you attach here your testing query (the one which is a few kilobytes)?
Example query (20 KB)
Logged In: YES
user_id=32013
Originator: YES
I forgot that Xdebug slows down things considerably, so several seconds for "a few" kilobytes was an exaggeration.
The attached (artificial) query is 20 KB and parses in about 5 seconds (or 23 seconds when Xdebug is enabled).
Here are the first few lines of an Xdebug profile of the code (the code is run with the optimizations in the attached patch):
Time self Time total #calls
Total 26.600
PMA_SQP_parse 12.777 23.488 1
PMA_STR_strInStr 6.135 6.135 71704
mb_substr 4.598 4.598 71769
PMA_STR_isSqlIdentifier 3.708 3.708 30753
PMA_SQP_analyze 2.036 2.039 1
mb_strpos 2.020 2.020 71704
PMA_SQP_arrayAdd 0.849 0.849 20511
ctype_alnum 0.808 0.808 30753
{main} 0.620 26.552 1
ctype_space 0.542 0.542 20497
ord 0.500 0.500 20488
strlen 0.481 0.481 20641
PMA_SQP_formatHtml 0.387 0.389 2
require_once 0.297 26.149 50
ctype_digit 0.278 0.278 10248
strspn 0.269 0.269 10241
...
File Added: bug1970836.sql
Logged In: YES
user_id=210714
Originator: NO
Thanks Christian for the query. I'm not saying that your optimizitions are not worthwhile, but on my machine (with APC opcode active), this query parses in 5 seconds, with or without the optimizations.
Logged In: YES
user_id=210714
Originator: NO
Patch merged for 3.0, thanks. Will use the profiler for further improvements.
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 14 days (the time period specified by
the administrator of this Tracker).