[Phpmyadmin-devel] SHOW DATABASES on ISPs

[Marc Delisle]

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?