Bug #15314 SHOW queries are slower in 5.0 than in 4.1
Submitted: 29 Nov 2005 13:42 Modified: 27 Sep 2008 12:39
Reporter: Alexey Kopytov Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Information schema Severity:S5 (Performance)
Version:5.0.13 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: affects_connectors

[29 Nov 2005 13:42] Alexey Kopytov 
Description:
This is a followup to bug #13931.  Below are the average numbers of queries per second over 3 benchmark runs:

4.1.14:
SHOW VARIABLES: 1785.06 
SHOW COLLATIONS: 1674.99

5.0.13:
SHOW VARIABLES: 807.50
SHOW COLLATIONS: 1086.73

Looking at the source code, 4.1 tree uses Protocal::store to store the results of SHOW queries, while 5.0 stores them in a temporary table using Field_*::store method, which is more expensive. This also means that all other SHOW queries are slower in 5.0.

How to repeat:
Benchmark the performance of SHOW queries in 4.1 and 5.0
[27 Sep 2008 12:39] Konstantin Osipov 
This was an intentional change to make sure SHOW queries use information_schema implementation, I doubt that we have any plans to fix this, apart from re-implementing information_schema.