Bug #29150 intolerably considerable performance degradation since MySQL 4.1.1
Submitted: 15 Jun 2007 23:26 Modified: 6 Feb 11:08
Reporter: Ze Cheng
Status: Analyzing
Category: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: Alexey Kishkin Target Version:
Tags: myisam tables, INDEX

[15 Jun 2007 23:26] Ze Cheng 
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
[15 Jun 2007 23:42] Ze Cheng 
4mysql.tar.bz2  file has been uploaded
[16 Jun 2007 12:12] Valeriy Kravchuk 
Thank you for a problem report. Had you uploaded that 4mysql.tar.bz2 to our FTP server?
[18 Jun 2007 19:06] Ze Cheng 
Yes, I had uploaded the 4mysql.tar.gz file to your ftp site. Please let me know if you
have trouble finding it.

thanks
[19 Jun 2007 22:39] Ze Cheng 
To be precise, the query works with MySQL 4.1.0-alpha, but not anything following it.