[Phpmyadmin-devel] SHOW DATABASES on ISPs

[Sebastian Mendel]

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 ... ;-)


> 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


-- 
Sebastian