Bug #11948 MyISAM only, nested JOIN Views, dramatic performance loss
Submitted: 14 Jul 2005 18:00 Modified: 3 Aug 2005 3:40
Reporter: Matthias Leich Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any

[14 Jul 2005 18:00] Matthias Leich
I have an abnormal complex testcase where the storage engines 
Memory and InnoDB clearly outperform MyISAM when a certain
complexity is reached.
The test with MyISAM shows an dramatic non linear increase of
CPU time consumption, but no significant disc I/O, paging or
increase of used virtual memory. InnoDB and MEMORY show a
nearly linear increase of consumed CPU time.
I am sure that the complexity of the testcase is abnormal, but
maybe somebody is able to invent a more real life like testcase
suffering from the same problem. 
The extreme annoying facts are that there are too many 
surprising effects:
- CPU time (user) in seconds (mysql-test-run activities included)
  Complexity level  MyISAM    MEMORY/InnoDB 
     10                      2.3               2.3
     20                     10               ~2.5  
     29                     28.8              2.8
     30                    230                2.9
     60         stopped by me          ~5.0
- omitting of some records containing a column with NULL
  --> no dramatic increase of CPU time
- CHAR(50) instead of VARCHAR(50)
  --> no dramatic increase of CPU time

Please have a look into the attached testcase. It contains
some test variants and the execution times I needed.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1922, 2005-07-13 18:23:20

How to repeat:
Please use the attached testcase ml037.test.

  copy it to mysql-test/t
  touch r/ml037.result     # Produce a dummy file with 
                                   # expected results
  ./mysql-test-run ml037
       and wait
[14 Jul 2005 18:01] Matthias Leich
test script

Attachment: ml037.test (application/test, text), 3.91 KiB.