MySQL Bugs: #47330: Slow query with left join and having.

Bug #47330	Slow query with left join and having.
Submitted:	15 Sep 2009 15:39	Modified:	23 Sep 2009 9:42
Reporter:	Peter Szekvolgyi	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.77, 5.1.34, 5.1.37	OS:	Linux (Debian x64)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
If I use left join (with et.szlaaz=es.szlaaz on parameter) with having (1=1 or anything) the query is slow over 2 million records in es.
But if i use with es.szlaaz=et.szlaaz on left join is fast.
MySQL 4.1.15 Server it's working fast every time with the slow and the fast query. I tested with innodb and myisam.

Only diff between slow and fast query is:
et.szlaaz=es.szlaaz <-> es.szlaaz=et.szlaaz

With having:
Slow query:
select et.raz, 0 as tnemzarttdt, 0 as zaro from et
left join egy2 es on et.tip = 1 and et.mad=es.datum and es.raz=et.raz and et.szlaaz=es.szlaaz and et.szaz=es.szaz
group by et.raz
having ( 1=1 );

Time (with SET SESSION query_cache_type = OFF):
real	0m2.990s
user	0m0.004s
sys	0m0.004s

Explain:
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	et	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	es	ref	raz,szaz,datum,szlaaz,raz_2	szlaaz	3	const	10	

Fast query:
select  et.raz, 0 as tnemzarttdt, 0 as zaro from et
left join egy2 es on et.tip = 1 and et.mad=es.datum and es.raz=et.raz and es.szlaaz=et.szlaaz and et.szaz=es.szaz
group by et.raz
having ( 1=1 );

Time (with SET SESSION query_cache_type = OFF):
real	0m0.013s
user	0m0.000s
sys	0m0.004s

Explain:
1	SIMPLE	et	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	es	ref	raz,szaz,datum,szlaaz,raz_2	raz_2	14	const,const,const,const	1	Using index

If i deleted having parameter:
The slow and fast query explain (running time are same and fast):
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	et	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	es	ref	raz,szaz,datum,szlaaz,raz_2	szaz	4	const	10

ET table has only one record:
INSERT INTO `et` VALUES (1,0,11,'2009-09-08',1,19868);

Table's:
CREATE TABLE `et` (
  `tip` bigint(20) NOT NULL DEFAULT '0',
  `eaz` bigint(20) NOT NULL DEFAULT '0',
  `raz` int(11) unsigned NOT NULL DEFAULT '0',
  `mad` date DEFAULT NULL,
  `szlaaz` bigint(20) NOT NULL DEFAULT '0',
  `szaz` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `egy2` (
  `egyaz` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `szaz` int(11) NOT NULL DEFAULT '0',
  `raz` int(10) unsigned NOT NULL DEFAULT '0',
  `szlaaz` mediumint(9) NOT NULL DEFAULT '0',
  `datum` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`egyaz`),
  KEY `raz` (`raz`),
  KEY `szaz` (`szaz`),
  KEY `datum` (`datum`),
  KEY `szlaaz` (`szlaaz`),
  KEY `raz_2` (`raz`,`szaz`,`szlaaz`,`datum`)
) ENGINE=InnoDB AUTO_INCREMENT=2516587 DEFAULT CHARSET=latin1;

How to repeat:
Every time i started the query without cache.

Thank you for the problem report. What about `egy2`'s content? Can you send a dump of its data?

Had you tried to change engine for `egy2` to MyISAM? I wonder if you'll see any difference in this case.

Yes, i tried MyISAM engine. The fast query speed is same, the slow is 1.5-2 sec.

Tomorrow i will put dump to web, but it's 70-80 MB. Or i try to write a script what generates data to egy2 table.

Script is OK, as soon as it adds data that allows to repeat this problem every time.

I changed ET table szlaaz from bigint(20) to mediumint(9). The slow and the fast query speed good, same and explain is same too, but in the originally query et data came from a subquery and it return bigint(20). Can I change the subquery data records type?

I will attach the generator script soon.

Data create script with table create

Attachment: generate.sh (application/octet-stream, text), 3.04 KiB.

I attached the data generator script for linux/unix.

Use:
./generate | mysql test

The run time of this script is long. ( It's generate over 2 million records. )
And default it's run create table for et and egy2 table. If it exist drop these table and create!

After the generator script done please run:
update egy2 SET szaz=1,szaz=19868 limit 214121;

Without this both query speed good.

Verified just as described using your script and steps described:

openxs@suse:/home2/openxs/dbs/5.0> sh /home2/openxs/generate.sh | bin/mysql -uroot test
openxs@suse:/home2/openxs/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.86-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update egy2 SET szaz=1,szaz=19868 limit 214121;
Query OK, 214118 rows affected (25 min 10.78 sec)
Rows matched: 214121  Changed: 214118  Warnings: 0

mysql> explain select  et.raz, 0 as tnemzarttdt, 0 as zaro from et left join egy2 es on et.tip = 1 and et.mad=es.datum and es.raz=et.raz and es.szlaaz=et.szlaaz and et.szaz=es.szaz group by et.raz having ( 1=1 );
+----+-------------+-------+--------+-----------------------------+-------+---------+-------------------------+------+-------------+
| id | select_type | table | type   | possible_keys               | key   | key_len | ref                     | rows | Extra       |
+----+-------------+-------+--------+-----------------------------+-------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | et    | system | NULL                        | NULL  | NULL    | NULL                    |    1 |             |
|  1 | SIMPLE      | es    | ref    | raz,szaz,datum,szlaaz,raz_2 | raz_2 | 14      | const,const,const,const |  206 | Using index |
+----+-------------+-------+--------+-----------------------------+-------+---------+-------------------------+------+-------------+
2 rows in set (0.01 sec)

mysql> explain select et.raz, 0 as tnemzarttdt, 0 as zaro from et
    -> left join egy2 es on et.tip = 1 and et.mad=es.datum and es.raz=et.raz and
    -> et.szlaaz=es.szlaaz and et.szaz=es.szaz
    -> group by et.raz
    -> having ( 1=1 );
+----+-------------+-------+--------+-----------------------------+--------+---------+-------+------+-------+
| id | select_type | table | type   | possible_keys               | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+--------+-----------------------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | et    | system | NULL                        | NULL   | NULL    | NULL  |    1 |       |
|  1 | SIMPLE      | es    | ref    | raz,szaz,datum,szlaaz,raz_2 | szlaaz | 3       | const |   10 |       |
+----+-------------+-------+--------+-----------------------------+--------+---------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> select et.raz, 0 as tnemzarttdt, 0 as zaro from et left join egy2 es on et.tip = 1 and et.mad=es.datum and es.raz=et.raz and et.szlaaz=es.szlaaz and et.szaz=es.szaz group by et.raz having ( 1=1 );
+-----+-------------+------+
| raz | tnemzarttdt | zaro |
+-----+-------------+------+
|  11 |           0 |    0 |
+-----+-------------+------+
1 row in set (3 min 4.51 sec)

Here we have the problem. egy2 table is big enough:

mysql> show table status like 'egy2';
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------+
| Name | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment               |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------+
| egy2 | InnoDB |      10 | Compact    | 2250558 |             39 |    88702976 |               0 |    287326208 |         0 |        2516587 | 2009-12-28 21:19:24 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 16384 kB |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------+
1 row in set (4.99 sec)

But the problem is NOT about bad statistics from InnoDB, as it is repeatable with MyISAM:

mysql> alter table egy2 engine=MyISAM;
Query OK, 2250084 rows affected (4 min 3.52 sec)
Records: 2250084  Duplicates: 0  Warnings: 0

mysql> select et.raz, 0 as tnemzarttdt, 0 as zaro from et left join egy2 es on et.tip = 1 and et.mad=es.datum and es.raz=et.raz and et.szlaaz=es.szlaaz and et.szaz=es.szaz group by et.raz having ( 1=1 );
+-----+-------------+------+
| raz | tnemzarttdt | zaro |
+-----+-------------+------+
|  11 |           0 |    0 |
+-----+-------------+------+
1 row in set (14.24 sec)

mysql> explain select et.raz, 0 as tnemzarttdt, 0 as zaro from et left join egy2 es on et.tip = 1 and et.mad=es.datum and es.raz=et.raz and et.szlaaz=es.szlaaz and et.szaz=es.szaz group by et.raz having ( 1=1 );
+----+-------------+-------+--------+-----------------------------+--------+---------+-------+------+-------+
| id | select_type | table | type   | possible_keys               | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+--------+-----------------------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | et    | system | NULL                        | NULL   | NULL    | NULL  |    1 |       |
|  1 | SIMPLE      | es    | ref    | raz,szaz,datum,szlaaz,raz_2 | szlaaz | 3       | const |   10 |       |
+----+-------------+-------+--------+-----------------------------+--------+---------+-------+------+-------+
2 rows in set (0.01 sec)

Workaround: use FORCE INDEX(raz_2).

If i remove having ( 1 = 1 ) or change et.szlaaz=es.szlaaz to es.szlaaz=et.szlaaz why fast? So it's not an optimizer bug?

In MySql 4.1.15 with MyISAM both query are fast.

Sorry, i read it was verified.