Sebastian Mendel a écrit :
Marc Delisle schrieb:
Sebastian Mendel a écrit :
Hi,
SHOW DATABASES seems somehow slow on ISPs
how about using I_S.SCHEMA_PRIVILEGES
f.e.
SELECT * FROM `SCHEMA_PRIVILEGES` LIMIT 0 , 30
performs much faster on the ISP i could test:
0.08 sec vs. 0.24 seconds
possible this will only work with ISPs where are no anonymous rights
possible a configuration where ISP can use I_S.SCHEMA_PRIVILEGES instead of SHOW DATABASES, or they can switch to SHOW DATABASES like '[user]_%' which performs much faster too
how about
$cfg['DatabaseListQuery'] = 'SHOW DATABASES'; // #user# is replaced by curent user $cfg['DatabaseListQuery'] = "SHOW DATABASES LIKE '#user#%'"; $cfg['DatabaseListQuery'] = 'SELECT DISTINCT TABLE_SCHEMA FROM `SCHEMA_PRIVILEGES`';
Maybe I don't understand your idea SHOW DATABASES LIKE 'user%'; naming databases with user names is only a convention, this is why SHOW DATABASES is better IMO in general. Promoting this in PMA could confuse a sysadmin into thinking that this is used to get the databases on which this user has access.
1.) they can tell PMA what command to use to fetch all databases for the user
2.) "SHOW DATABASES LIKE 'user%'" is only an example, some ISPs name thier user databases according to the user user: wp123 has dbs user123_1, user123_2 aso.
For your last idea, I think you meant SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES.
of course, but information_schema not INFORMATION_SCHEMA ... ;-)
Of course ;)
I'll make some tests on servers with a various number of databases.
Of course, picking the best default value for $cfg['DatabaseListQuery'] depends on the results of our tests.
i tent to use SHOW DATABASES still as default, but give the ISPs an easy way to change this to speed up things
Ok then, please implement this, with this default. It's up to the sysadmin to experiment and pick the best option for his server.
Hmmm, this makes me think: this option should be under $cfg['Servers'][$i] ? Suppose a 5.0.x server reacts differently than a 5.1.x or a 6.0.x one?