Bug #32342 subselect_sj very slow on 32bit
Submitted: 13 Nov 2007 19:51 Modified: 1 May 2008 22:27
Reporter: Philip Stoev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:mysql-6.0.3-alpha-linux-i686-glibc23 OS:Linux
Assigned to: Sergey Petrunya CPU Architecture:Any
Triage: D3 (Medium) / R2 (Low) / E4 (High)

[13 Nov 2007 19:51] Philip Stoev
Description:
In 6.0.3, the subselect_sj test takes exorbitant ammount of time to complete at 100% before it times out. This happens on 64 and 32bit machines, if the 32 bit binary is used. If the 64 bit binary is used on 64 bit machines, the test passes perfectly fast.

This test runs EXPLAIN on a subselect. The subselect appears to be fully materialized in order to answer the EXPLAIN, and since it does full join on several tables, this takes an enormous amount of time.

It appears to me that the code that optimizes EXPLAIN to run without materializing the subselect somehow did not make it into the 32bit binary distribution.

How to repeat:
Download mysql-6.0.3-alpha-linux-i686-glibc23.tar.gz, unpack and run

# cd mysql-test
# perl mysql-test-run.pl subselect_sj

do the same with 64 bit binaries and observe the difference.

Suggested fix:
Make 32 bit binaries run as fast as a 64 bit. Compile both from scratch from a freshly checked-out tree.
[1 May 2008 22:27] Sergey Petrunya
Not repeatable anymore (looked at mysql-6.0-opt) after we've fixed a number of crash/overrun bugs related to 6.0 subquery optimizations. Changing status to cant repeat.