Bug #39433 poor optimized query

• Submitted: 13 Sep 2008 17:52
• Modified: 21 Mar 2011 4:15
• Reporter: Roberto Spadim (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.45, 5.0.70, 5.1.30
• OS: Linux
• CPU Architecture: Any
• Tags: qc

[13 Sep 2008 17:52] Roberto Spadim

Description:
poor optimized plan

How to repeat:
query and table in files

Suggested fix:
check a better plan, maybe first 
select distinct field moeda and after check subquery

[15 Sep 2008 7:23] Valeriy Kravchuk

Thank you for a problem report. Verified on your test case with latest 5.0.70 from bzr. Adding FORCE INDEX(moeda) makes query to run hundreds times faster...

[15 Sep 2008 20:16] Roberto Spadim

FORCE INDEX worked on my mysql server version
thanks guys

[24 Dec 2008 12:00] Evgeny Potemkin

An example showing a wrong result. Select list should be evaluated only once per group, but the first select with GROUP BY evaluates it for each row.
The second query evaluates it correctly:

mysql> delimiter |
mysql> drop function bug39433|
Query OK, 0 rows affected (0.00 sec)

mysql> create function bug39433() returns int  begin set @var39433=(@var39433 + 1); return 0; end|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> create table t1 (f1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1),(1),(1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select @var39433:=0;
+--------------+
| @var39433:=0 |
+--------------+
|            0 | 
+--------------+
1 row in set (0.01 sec)

mysql> select bug39433(),f1 from t1 group by f1;
+------------+------+
| bug39433() | f1   |
+------------+------+
|          0 |    1 | 
+------------+------+
1 row in set (0.00 sec)

mysql> select @var39433;
+-----------+
| @var39433 |
+-----------+
|        24 | 
+-----------+
1 row in set (0.00 sec)

mysql> select @var39433:=0;
+--------------+
| @var39433:=0 |
+--------------+
|            0 | 
+--------------+
1 row in set (0.00 sec)

mysql> select bug39433(),f1, count(*) from t1 group by f1;
+------------+------+----------+
| bug39433() | f1   | count(*) |
+------------+------+----------+
|          0 |    1 |       24 | 
+------------+------+----------+
1 row in set (0.00 sec)

mysql> select @var39433;
+-----------+
| @var39433 |
+-----------+
|         1 | 
+-----------+
1 row in set (0.00 sec)

mysql>

[21 Mar 2011 4:15] Roberto Spadim

closed it's something similar to bug 24770