I recently upgraded phpmyadmin from 3.x to 4.x in a DB setup that has about 25,000 DBs (though only a small number of per-page DBs are visible, due to our only_db setting), and found that page load times were in the realm of 60-90 seconds each -- agonizingly slow (we run MySQL 5.5, btw). I traced the cause down to all the counting and selecting against the information_schema DB being done for DB names: anytime an explicit name isn't mentioned (e.g. LIKE is used) the query took 20, 40, even 60 seconds to complete.
I eventually made enough changes to the code to get page loads down to normal, and will mention some of the things I discovered:
- The only SELECT of information_schema's DB names that is quick is an exact match against SCHEMA_NAME w/o any charset transformation (i.e. no use of BINARY casts).
- Even a SELECT using IN('db1', 'db2') is super slow.
- Doing SELECT queries with a restricted (non-super) user is slower than the super-user (probably due to all the security checks that the super-user can avoid) so don't be fooled by any speed tests you run using a root login.
- Doing a COUNT() of DB info in the information_schema is just as super slow as a SELECT.
- The use of SHOW DATABASES LIKE is quite fast.
Thus, for a general fix, I'd recommend that you change the code that does any DB-name matching to do a SHOW DATABASES LIKE call, get the list of DB names, and then loop over it with individual-DB-name SELECTs into information_schema.
FYI, the fixes I did were a simpler set of changes that work for our particular site. If you're curious, here's what I did:
- I changed all the count operations (e.g. getPresence() calls) into array-length operations of the only_db array (since that is always a full list of the user's tables in our setup).
- I removed all BINARY casts in the database-name matching code.
- I removed all use of LIKE in the database-name matching code.
- I changed the code path in getDatabasesFull() that lists all DBs (when $database isn't specified) into a loop over the only_db list of DB names, adding each individual DB lookup into the $databases[$db_name] array.