MySQL Bugs: #391: Bad performance using datetime format with join clause under solaris

Bug #391	Bad performance using datetime format with join clause under solaris
Submitted:	7 May 2003 9:24	Modified:	13 May 2003 5:35
Reporter:	Christian Bonnet	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Solaris (solaris 2..8)
Assigned to:		CPU Architecture:	Any

Description:
hi,

We encounter something odd using mysql under solaris and concerning datetime format, join and request using "between".
I think we hit a bug...

Here is the script of the table :

CREATE TABLE MEASURE_Q0 (
  PLATFORM_ID tinyint(3) unsigned NOT NULL default '0',
  PARAMETER_CODE varchar(5) NOT NULL default '',
  PARAMETER_ID smallint(5) unsigned NOT NULL default '0',
  MEASURE_DATE datetime NOT NULL default '0000-00-00 00:00:00',
  MEASURE_DATE_UT datetime NOT NULL default '0000-00-00 00:00:00',
  VALUE float NOT NULL default '-8888',
  DATASTATE_CODE char(3) NOT NULL default '0',
  QUALITY_CODE char(1) NOT NULL default '0',
  MIN_DATE datetime NOT NULL default '0000-00-00 00:00:00',
  MAX_DATE datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (PLATFORM_ID,PARAMETER_ID,MEASURE_DATE),
) TYPE=InnoDB;

We create 3 index for speed up the request

Create Index idx_MEASURE_DATE_UT on MEASURE_Q0(MEASURE_DATE_UT) ;
Create Index idx_MIN_DATE on MEASURE_Q0(MIN_DATE) ;
Create Index idx_MAX_DATE on MEASURE_Q0(MAX_DATE) ;

The object of our request is to find data that are next to other by the time (in an interval determine by the min_date and the max_date columns)

when we run the resquest, the table contains 15000 rows

select t1.PLATFORM_ID,t1.PARAMETER_ID,t1.MEASURE_DATE,t1.MEASURE_DATE_UT,t1.VALUE, "MEASUREMENTS DATES ARE TOO SIMILAR","MQ0" from MEASURE_Q0 t1, MEASURE_Q0 t2 where t1.PLATFORM_ID = t2.PLATFORM_ID and t1.PARAMETER_ID = t2.PARAMETER_ID and t1.MEASURE_DATE_UT <> t2.MEASURE_DATE_UT and t1.MEASURE_DATE_UT between  t2.MIN_DATE AND t2.MAX_DATE;

If we run this request, we have to wait more than 20 minutes for the answer with a SUN Blade 2000, Processeur 900mhz, 2Go RAM and SunOS 5.8 (Solaris 2.8) with more than 600 M of memory dedicated to MySQL and no other "heavy" programs running during the stamment (MySQL use more than 90% of the cpu).
Under Windows 2000 with a Pentium 1,8Ghz and 512M of RAM, the request give us the answer in approximately 2 minutes and with an old PC (200MHz and 64M of RAM), we have the answer in 5 minutes !!! We test it also with other database server (Sybase) and obtain the result in 2 minutes

The more stange, and the thing that let us think about a bug and not a problem in our configuration of mysql under solaris (we don't have any problems of performance except this request) is that if we change the type of the columns MEASURE_DATE_UT, MIN_DATE and MAX_DATE and chose varchar(20), we have the result of the request in less than 2 minutes !!!

During our research of an explanation, we try every things as changing the type of the table for MyISAM, modify parameters in my.cnf, test it under other solaris server... But nothing change.

If you have an explanation !!!

How to repeat:

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

This is not a repeatable bug that is in the realm of this bug find program, which is directed to find critical bugs that should be fixed in the next release MySQL.

To get help for this, we recommend you to consider MySQL support (http://www.mysql.com/support-and-consulting.html).

Another option is to upload a fully repeatable test case that we can play with to ftp://support.mysql.com/pub/mysql/secret, but we can't promise that this will be fixed in the next MySQL version, just that we will consider fixing it in a future MySQL version.

The MySQL manual documents how you can use EXPLAIN and SHOW STATUS on the the different systems to get a better picture of what is going on.  This may help you find a workaround for this problem.