| 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: | |
| 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
[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...
