Bug #16081 SLOW SELECT
Submitted: 30 Dec 2005 0:19 Modified: 16 Jun 2011 16:21
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.0.19-BK, 5.0.15 OS:Linux (LINUX)
Assigned to: Valeriy Kravchuk CPU Architecture:Any

[30 Dec 2005 0:19] Roberto Spadim
Description:
SELECT WITH (col1,col2,col3)=(colt1,colt2,colt3) slower than
col1=colt1 and col2=colt2 and col3=colt3

How to repeat:
SELECT c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,b.cliente_id 
FROM est_mov AS a, spamov AS b, estoque_itens AS c 
WHERE 
(b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_spa) AND 
(c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red) AND a.lote_tipo="v"
group by a.item_id,a.item_id_red

Suggested fix:
???
[30 Dec 2005 13:38] Valeriy Kravchuk
Thank you for a problem report. Sorry, but there is no sql file attached, so, please, send the results of SHOW CREATE TABLE for est_mov, spamov and  estoque_itens tables, as well as any data to repeat the problem you described. Have you tried to use newer version, 5.0.17?
[30 Dec 2005 13:52] Roberto Spadim
sorry that was to big

Attachment: shared3.zip (octet/stream, text), 62.41 KiB.

[3 Jan 2006 18:20] Roberto Spadim
i didn't test with 5.0.18 yet
windows 5.0.16 have the same error
[11 Jan 2006 18:12] Valeriy Kravchuk
Verified just as described on your test case (uploaded) with 5.0.19-BK ():

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> explain SELECT
    -> c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,
    -> b.cliente_id
    -> FROM est_mov AS a, spamov AS b, estoque_itens AS c
    -> WHERE
    -> (b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_s
pa)
    -> AND
    -> (c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red)
    -> AND a.lote_tipo="v"
    -> group by a.item_id,a.item_id_red;
+----+-------------+-------+-------+---------------+------+---------+------+----
--+---------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | row
s | Extra                           |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+---------------------------------+
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL | NULL    | NULL |  24
0 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | ALL   | NULL          | NULL | NULL    | NULL |  66
0 |                                 |
|  1 | SIMPLE      | a     | index | NULL          | lote | 80      | NULL | 129
9 | Using where; Using index        |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+---------------------------------+
3 rows in set (0.00 sec)

mysql> explain SELECT
    -> c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,
    -> b.cliente_id
    -> FROM est_mov AS a, spamov AS b, estoque_itens AS c
    -> WHERE
    -> b.unidade_id = a.unidade_id
    -> AND b.lote_tipo = a.lote_tipo
    -> AND b.lote_spa = a.lote_spa
    -> AND c.plano_conta_id = a.item_id
    -> AND c.plano_conta_id_red = a.item_id_red
    -> AND a.lote_tipo="v"
    -> group by a.item_id,a.item_id_red;
+----+-------------+-------+--------+-------------------------------------------
------------+---------+---------+-------------------------------------------+---
---+-----------------------------------------------------------+
| id | select_type | table | type   | possible_keys
            | key     | key_len | ref                                       | ro
ws | Extra                                                     |
+----+-------------+-------+--------+-------------------------------------------
------------+---------+---------+-------------------------------------------+---
---+-----------------------------------------------------------+
|  1 | SIMPLE      | a     | index  | PRIMARY,lote,oe
            | lote    | 80      | NULL                                      |  9
75 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,id,plano_conta_numero
            | id      | 16      | test3.a.item_id,test3.a.item_id_red       |
 1 |                                                           |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY,spamov_unidade_id,spamov_oe,spamov
_op,estorno | PRIMARY | 17      | test3.a.unidade_id,const,test3.a.lote_spa |
 1 | Using where                                               |
+----+-------------+-------+--------+-------------------------------------------
------------+---------+---------+-------------------------------------------+---
---+-----------------------------------------------------------+
3 rows in set (0.01 sec)

So, we get different query plans. First query works for ages, accodingly. Second  returned 96 rows in 0.14 sec. for me. It is a bug, because since the following works:

mysql> select (1,2,3) = (1,2,3);
+-------------------+
| (1,2,3) = (1,2,3) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

it should be optimized just as equivalent set of expressions ANDed together.
[21 Feb 2006 12:25] Lukas Smith
I am seeing similar issues with 5.0.16-nt. See attached file "subselect_testcase.txt"
[1 Sep 2006 11:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11247

ChangeSet@1.2272, 2006-09-01 04:23:04-07:00, igor@rurik.mysql.com +5 -0
  Fixed bug #16081: row equalities were not taken into
  account by the optimizer.
  Now all row equalities are converted into conjunctions of
  equalities between row elements. They are taken into account
  by the optimizer together with the original regular equality
  predicates.
[19 Sep 2006 8:30] Georgi Kodinov
Pushed into 5.0.26/5.1.12-beta
[25 Sep 2006 20:09] Paul DuBois
Noted in 5.0.26, 5.1.12 changelog.
[8 Apr 2011 21:35] Roberto Spadim
hi i was reading facebook coments about this bug, maybe it still open
could anyone check it?

http://www.facebook.com/note.php?note_id=243134480932
[16 Jun 2011 13:39] Valeriy Kravchuk
If you suspect that this bug is not fixed in current versions (5.0.92, 5.1.57 or 5.5.13), please, send test case that proves this. Your original *.sql can not be loaded without errors in current versions, so I can not check myself.
[16 Jun 2011 14:44] Roberto Spadim
i think it's ok... 0.1sec in a 70 rows result, i will test with bigger result and post again...

TIMES:
SELECT c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,b.cliente_id  FROM est_mov AS a, spamov AS b, estoque_itens AS c  WHERE  (b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_spa) AND  (c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red) AND a.lote_tipo="v" group by a.item_id,a.item_id_red;

Duration for 1 query: 1,610 sec.

SELECT c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,b.cliente_id  FROM est_mov AS a, spamov AS b, estoque_itens AS c  WHERE  #(b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_spa) AND  #(c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red)   b.unidade_id=a.unidade_id AND b.lote_tipo=a.lote_tipo AND b.lote_spa=a.lote_spa and c.plano_conta_id=a.item_id and c.plano_conta_id_red=a.item_id_red  AND a.lote_tipo="v" group by a.item_id,a.item_id_red;

Duration for 1 query: 1,531 sec.
[16 Jun 2011 15:25] Roberto Spadim
with 13 results... i think we have a problem... (0.5sec vs 0.062sec)
i will test in a big result, just some time to get it...
ahh! mysql 5.5 not 5.0

SELECT c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,b.cliente_id  FROM est_mov AS a, spamov AS b, estoque_itens AS c  WHERE  #(b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_spa) AND  #(c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red)   b.unidade_id=a.unidade_id AND b.lote_tipo=a.lote_tipo AND b.lote_spa=a.lote_spa and c.plano_conta_id=a.item_id and c.plano_conta_id_red=a.item_id_red  AND a.lote_tipo="v" group by a.item_id,a.item_id_red  
/* 0 rows affected, 13 rows found. Duration for 1 query: 0,563 sec. */
SELECT c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,b.cliente_id  FROM est_mov AS a, spamov AS b, estoque_itens AS c  WHERE  (b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_spa) AND  (c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red)   #b.unidade_id=a.unidade_id AND b.lote_tipo=a.lote_tipo AND b.lote_spa=a.lote_spa and #c.plano_conta_id=a.item_id and c.plano_conta_id_red=a.item_id_red  AND a.lote_tipo="v" group by a.item_id,a.item_id_red  
/* 0 rows affected, 13 rows found. Duration for 1 query: 0,062 sec. */
[16 Jun 2011 16:07] Roberto Spadim
i think it's ok.. (est_mov=9GB,14000000rows myisam table, spamov=500MB,540000rows myisam table, estoque_itens=2MB,3474rows myisam file)

1)SHOW status LIKE 'Handler_read%';
2) SELECT SQL_NO_CACHE c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,b.cliente_id  FROM est_mov AS a, spamov AS b, estoque_itens AS c  WHERE  (b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_spa) AND  (c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red)   #b.unidade_id=a.unidade_id AND b.lote_tipo=a.lote_tipo AND b.lote_spa=a.lote_spa and #c.plano_conta_id=a.item_id and c.plano_conta_id_red=a.item_id_red  AND a.lote_tipo="v" group by a.item_id,a.item_id_red  limit 1;
/* 0 rows affected, 1 rows found. Duration for 1 query: 0,282 sec. */
3)SHOW status LIKE 'Handler_read%';
4) SELECT SQL_NO_CACHE c.plano_conta_numero,c.descricao,b.vendedor_tipo,b.vendedor_id,b.cliente_tipo,b.cliente_id  FROM est_mov AS a, spamov AS b, estoque_itens AS c  WHERE  #(b.unidade_id,b.lote_tipo,b.lote_spa)=(a.unidade_id,a.lote_tipo,a.lote_spa) AND  #(c.plano_conta_id,c.plano_conta_id_red)=(a.item_id,a.item_id_red)   b.unidade_id=a.unidade_id AND b.lote_tipo=a.lote_tipo AND b.lote_spa=a.lote_spa and c.plano_conta_id=a.item_id and c.plano_conta_id_red=a.item_id_red  AND a.lote_tipo="v" group by a.item_id,a.item_id_red  limit 1;
/* 0 rows affected, 1 rows found. Duration for 1 query: 0,359 sec. */
5)SHOW status LIKE 'Handler_read%';

RESULTS:
Variable_name		Value1	Value2	Value3		Dif 1	Dif 2
Handler_read_first	12	14	16		2	2
Handler_read_key	19854	23163	26472		3309	3309
Handler_read_next	216954	253113	289272		36159	36159
Handler_read_prev	0	0	0		0	0
Handler_read_rnd	0	0	0		0	0
Handler_read_rnd_next	5	5	5		0	0
[16 Jun 2011 16:12] Valeriy Kravchuk
So, we still can assume that this specific case is fixed.
[16 Jun 2011 16:14] Roberto Spadim
yes it's closed, i will try to comment on block list.. thanks guys
[16 Jun 2011 16:21] Roberto Spadim
i will open another bug, for OR optimization...