MySQL Bugs: #21348: Memory storage engine poor performance relative to 4.1.20 and HASH vs BTREE

Bug #21348	Memory storage engine poor performance relative to 4.1.20 and HASH vs BTREE
Submitted:	30 Jul 2006 10:37	Modified:	30 Sep 2006 12:46
Reporter:	Edgar Scrase	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.21	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	HASH instead of BTREE, Indexing, Memory Engine, Poor Performance

Description:
We have compared using HASH and BTREE indexes in memory tables for versions 4.1.20 and 5.0.21.  With version 4.1 there is a significant difference between the two index types, btrees being much faster as there are a lot of range comparisons in the query.  Hwever, there is no difference between HASH and BTREE times for 5.0.X __AND__ 5.0.X is considerably slower than 4.1.X to process the same query.

The times given are for two tables, with the structure shown in "How To Repeat", and a chunk of our code, the bulk of which is to run the select given in "How To Repeat".  The tables contained between 6-50,000 records.  To run using HASH indexes, simply change all occurrences of Btree FOR HASH.

4.1.20 took 187 seconds to process for BTREE and 265 for HASH
5.0.21 took 312 seconds to process for BTREE and 312 for HASH

Is there another, more efficent way to run this query in 5.0.x as I notice that there are lots of other performance improvements that we would really like to take advantage of?

Thank you!

How to repeat:
SELECT a.UOR, b.UOR FROM Coords_Blocks_VM a, Coords_PolyLineRoads_VM b WHERE a.Min_X<=b.Max_X AND a.Max_X>=b.Min_X AND a.Min_Y<=b.Max_Y AND a.Max_Y>=b.Min_Y;

CREATE TABLE `Coords_PolyLineRoads_VM` (  `ID` int(11) NOT NULL ,  `UOR` int(11) default NULL,  `Object_ID` varchar(50) default NULL,  `Centroid_X` int(11) default NULL,  `Centroid_Y` int(11) default NULL,  `Min_X` int(11) default NULL,  `Max_X` int(11) default NULL,  `Min_Y` int(11) default NULL,
`Max_Y` int(11) default NULL,      PRIMARY KEY  (`ID`),  KEY `UOR` (`UOR`),  KEY `Object_ID` (`Object_ID`),  INDEX USING BTREE (Centroid_X),  INDEX USING BTREE (Centroid_Y),  INDEX USING BTREE (Min_X)
,  INDEX USING BTREE (Max_X),  INDEX USING BTREE (Min_Y),  INDEX USING BTREE (Max_Y)) ENGINE=HEAP DEFAULT CHARSET=latin1

Suggested fix:
Check the relevant code sections for 5.0.21 and 4.1.20 for differences to ensure that the index type flag is being processed correctly for memory tables?

Is the optimiser configured correctly for Memory tables?

Thank you for a problem report. Please, upload the data that will help to demonstrate the behaviour described. At least, please, send SHOW TABLE STATUS results for the problematic table.

Here are the table statuses for three memory tables that we join in pairs using the query above.  These start with the prefix "Coords".  The "Temp" table is the product of one of these joins. 

"coords_itnblocks_vm","MEMORY",10,"Fixed",6544,84,598608,259059528,1002744,0,,"","","","latin1_swedish_ci",,"",""
"coords_itnstreets_vm","MEMORY",10,"Fixed",13059,84,1197728,259059528,1898898,0,,"","","","latin1_swedish_ci",,"",""
"coords_itnstreetsegments_vm","MEMORY",10,"Fixed",38733,84,3474384,259059528,5647374,0,,"","","","latin1_swedish_ci",,"",""
"temp_itnstreettosegment","MEMORY",10,"Fixed",25061,265,6780392,627453220,1052562,0,,"","","","latin1_swedish_ci",,"",""

Hope this helps.  The data is not ours, otherwise I would upload this as well.  To replicate, simply fill the Min and Max X & Y columns in the coords tables with 5-7 digit integers in the same approximate region (easy to generate in a spreadsheet package).

Please, send the results of SHOW CREATE TABLE for Coords_Blocks_VM table also. Are you able to repeat the behaviour described with a newer version, 5.0.24?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".