#235 query for routines is useless and gets a lot of time

pending-accepted
MySQL (68)
5
2013-04-27
2012-04-07
No

Hi!

Every time I change from a db to another, adminer does a

SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'newdb'

In my server I have over 700 databases and this query takes like a long time, over 1 minute and 30 seconds, just to change to another db. The query doesn't serve any purpose at all and takes a long a time, making adminer (and phpmyadmin btw) useless!

It's not required, except if I'm doing an export of procedures or editing any of them.

Please, cut it out!

M.

MySQL 5.1.37-community
-------------------------------------

mysql> use information_schema;
Database changed
mysql> select count(*) from routines;
+----------+
| count(*) |
+----------+
| 23232 |
+----------+
1 row in set, 5 warnings (1 min 17.05 sec)

mysql> SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'mikrocrm_99';
+----------+
| count(*) |
+----------+
| 55 |
+----------+
1 row in set, 5 warnings (1 min 28.75 sec)

Discussion

  • Jakub Vrána

    Jakub Vrána - 2012-05-14

    Adminer doesn't execute SELECT count(*), it executes SELECT * to get the list of routines in the database. I don't want to remove this feature (or load the list on a click) but I accept the request for improving its performance.

    Can you please try running SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS on your box and measure the time to run them? If they will be significantly faster then I will exchange them for the information_schema.ROUTINES query.

    If they will be slow too then I will probably make the call deferred.

     
  • Jakub Vrána

    Jakub Vrána - 2012-05-14
    • status: open --> open-accepted
     
  • Jakub Vrána

    Jakub Vrána - 2013-04-27
    • status: open-accepted --> pending-accepted
     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks