#2912 (ok 3.2.3) Slow export when having lots of databases
When using phpMyAdmin 3.1.5 with MySQL 5.0.32 and lots of bases (> 4000), it is very slow to export data.
I had previously a problem of slow browsing when using 2.9.1 but it was solved by using 3.1.5 and the DisableIS parameter.
The "SHOW PROCESSLIST" in mysql tell me that MySQL spend lot of time in various select of information_schema, for example more than 77 seconds to execute this request:
SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION_STATEMENT, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA= 'testingbaseslow' and EVENT_OBJECT_TABLE = 'evo_items__itemtag
my provider doesn't want to update MySQL to 5.1.22 and I didn't see any improvement for the export in phpMyAdmin 3.2.0.1 changelog.
Discussion
Bonjour Stéphane,
I tried with MySQL 5.1.37 and it's not better. If DisableIS is true, I could generate a SHOW TRIGGERS statement which returns an answer immediately, however the MySQL manual says that shis statement requires the TRIGGER privilege. In 5.1.37 it's not true but I'm not sure if we should go this way. Comments?
I retested and the manual is correct: to obtain the information about trigger, a user needs the TRIGGER privilege. This applies for both INFORMATION_SCHEMA and SHOW TRIGGERS so I'll implement the proposed fix.
Fixed, please confirm with this patch:
http://phpmyadmin.svn.sourceforge.net/viewvc/phpmyadmin/branches/QA_3_2/phpMyAdmin/libraries/database_interface.lib.php?r1=12997&r2=12996&pathrev=12997
Worked fine here, I was able to export my data almost instantly.