MySQL Bugs: #61541: SLOW QUERY

Bug #61541	SLOW QUERY - REWRITE
Submitted:	16 Jun 2011 16:26	Modified:	18 Jun 2011 13:35
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Hi guys, continuing bug 16081
about facebook problem
http://www.facebook.com/note.php?note_id=243134480932

maybe a wrong rewrite about this query, or wrong understanding of what mysql optimizer do... i will post queries in files

How to repeat:
1)show status like 'Handler_read%';
2)select * from estoque_itens where (plano_conta_id,plano_conta_id_red) in ((77,348),(77,-98)) ;
/* 0 rows affected, 2 rows found. Duration for 1 query: 0,078 sec. */
3)show status like 'Handler_read%';
4)select * from estoque_itens where  (plano_conta_id=77 AND plano_conta_id_red=348) or  (plano_conta_id=77 AND plano_conta_id_red=-98);
/* 0 rows affected, 2 rows found. Duration for 1 query: 0,172 sec. */
5)show status like 'Handler_read%';
6)select * from estoque_itens where (plano_conta_id,plano_conta_id_red)=(77,348) OR (plano_conta_id,plano_conta_id_red)=(77,-98);
/* 0 rows affected, 2 rows found. Duration for 1 query: 0,079 sec. */
7)show status like 'Handler_read%';

RESULTS:

Variable_name		Value1	Value2	Value3	Value4	DIFF1	DIFF2	DIFF3
Handler_read_first	4	4	4	4	0	0	0
Handler_read_key	123040	123040	123042	123044	0	2	2
Handler_read_next	706162	706162	706164	706166	0	2	2
Handler_read_prev	0	0	0	0	0	0	0
Handler_read_rnd	64	64	64	64	0	0	0
Handler_read_rnd_next	1727	3049	3049	3049	1322	0	0

Suggested fix:
second and third queries are near optimized, but the first isn't... maybe because IN operator... maybe a rewrite of small number of values could help... (2 'in' values)

create table, and data to test

Attachment: tmp.zip (application/x-zip-compressed, text), 38.80 KiB.

facebook have a open bug, if want to link this with that... (test files)
facebook bug 31188

I think this is just a duplicate of/another test case for Bug #31188.