MySQL Bugs: #16081: SLOW SELECT

Bug #16081	SLOW SELECT
Submitted:	30 Dec 2005 1:19	Modified:	25 Sep 2006 22:09
Reporter:	Roberto Spadim (Basic Quality Contributor)
Status:	Closed
Category:	Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.19-BK, 5.0.15	OS:	Linux (LINUX)
Assigned to:	Bugs System	Target Version:

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:
???

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?

sorry that was to big

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

i didn't test with 5.0.18 yet
windows 5.0.16 have the same error

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.

I am seeing similar issues with 5.0.16-nt. See attached file "subselect_testcase.txt"

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.

Pushed into 5.0.26/5.1.12-beta

Noted in 5.0.26, 5.1.12 changelog.