#1179 (ok 4.4) Speed up slow triggers by using EVENT_OBJECT_SCHEMA
STRATO hosted Server - MySQL 5.0.67-log
I found another very slow query when pma-browsing through the structures, the last query for the triggers:
[count] => 1
[query] => SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION_STATEMENT, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA= 'mydbname' and EVENT_OBJECT_TABLE = 'mytablename';
[time] => 5.757780790329
[trace] => Array
[0] => ./libraries/database_interface.lib.php#1109: PMA_DBI_try_query()
[1] => ./libraries/database_interface.lib.php#1331: PMA_DBI_fetch_result()
[2] => ./libraries/tbl_triggers.lib.php#14: PMA_DBI_get_triggers()
[3] => ./tbl_structure.php#811: require()
I also found the reason for the slow triggers. That was changed in pma somewhere between 3.0.0. and 3.2.1
In pma 3.0.0. the function PMA_DBI_get_triggers() takes less than a second in the same environment.
The implementation in pma 3.0.0 used
WHERE EVENT_OBJECT_SCHEMA='mydbname'
In pma 3.2.1. the query is built with
WHERE TRIGGER_SCHEMA= 'mydbname'
According to
http://dev.mysql.com/doc/refman/5.0/en/triggers-table.html
it should be safe to use both:
"The TRIGGER_SCHEMA [...] contain the name of the database in which the trigger occurs [...]"
"[...]every trigger is associated with exactly one table. The EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE columns contain the database in which this table occurs, and the table's name."
So, why not use EVENT_OBJECT_SCHEMA instead of TRIGGER_SCHEMA?
It seems to be much faster on at least one big hoster's servers.
best regards,
Markus
see also
https://sourceforge.net/forum/forum.php?thread_id=1889931&forum_id=72909
Fixed with https://github.com/phpmyadmin/phpmyadmin/commit/76e3ec78c2b086a487e7495eafeb3d6796a1eddc