MySQL Bugs: #29150: intolerably considerable performance degradation since MySQL 4.1.1

Bug #29150	intolerably considerable performance degradation since MySQL 4.1.1
Submitted:	15 Jun 2007 21:26	Modified:	25 Feb 2014 18:16
Reporter:	Ze Cheng	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S5 (Performance)
Version:	above 4.1.1	OS:	Linux (2.6.14.2-95.caos.numa 2005 x86_64 x86_64 x86_64 GNU/Linux and 2.6.9-5.ELsmp i686 i686 i386 GNU/Linux)
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, myisam tables

Description:
A query that updates 2 fields of a table based on the fields of another table ran very well (finishing within 2-3 min ) with MySQL server version-ed <= 4.1.1.

With the same input, the same query never ends with MySQL server version-ed > 4.1.1 (like 4.1.21 or 5.0)

Here is the query: (the inputs are attached to recover the scenario)

UPDATE majortable t, alltable a1, alltable a2

SET t.lfStarts=CONCAT(a1.tStart, ',', a2.tStart), 
t.lfSizes=CONCAT(a1.blockSizes, REPLACE(a2.blockSizes, ',', ''))

WHERE 
a1.qName=SUBSTRING_INDEX(t.lfNames, ',', 1) AND                               a2.qName=SUBSTRING_INDEX(t.lfNames, ',', -1) 

AND  a1.tName=t.chrom AND a2.tName=t.chrom AND 
( 
(t.chromStart=a1.tStart AND t.chromEnd=a2.tEnd) 
OR 
(t.chromStart=a2.tStart AND t.chromEnd=a1.tEnd) 
);

How to repeat:
please run the following under different versions of MySQL server

tar -jxvf 4mysql.tar.bz2
then
./repeat.sh    user   pswd    DBname

Requirements:
1. user has full privilege in DB
2. PHP installed on the server

4mysql.tar.bz2  file has been uploaded

Thank you for a problem report. Had you uploaded that 4mysql.tar.bz2 to our FTP server?

Yes, I had uploaded the 4mysql.tar.gz file to your ftp site. Please let me know if you have trouble finding it.

thanks

To be precise, the query works with MySQL 4.1.0-alpha, but not anything following it.

Thank you for the report.

Person who analyzed this bug could not repeat described behavior and I have no access to the file you provided, therefore closing the report as "Can't repeat". If you disagree please try to provide test case second time.