MySQL Bugs: #70705: Performance impact of row constructors is not properly documented

Bug #70705	Performance impact of row constructors is not properly documented
Submitted:	23 Oct 2013 15:42	Modified:	21 Mar 2016 16:04
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
This manual page, http://dev.mysql.com/doc/refman/5.6/en/row-subqueries.html, gives definition of row constructor and explains one use case:

"Row constructors are legal in other contexts. For example, the following two statements are semantically equivalent (and are handled in the same way by the optimizer):

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;"

But it does not say anything about the way row constructors are used for comparisons other than equals (=), while indexes are NOT used properly in these cases (see "How to repeat"). I was not able to find this explained anywhere in the manual.

How to repeat:
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.14-log |
+------------+
1 row in set (0.00 sec)

mysql> create table te(c1 int, c2 int, c3 int, c4 char(100), primary key(c2,c1,c3)) engine=InnoDB;
Query OK, 0 rows affected (2.85 sec)

mysql> insert into te values (1,1,1,'a'), (2,1,2,'b'),(3,1,3,'c');
Query OK, 3 rows affected (0.27 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into te values (1,2,1,'a'), (2,2,2,'b'),(3,2,3,'c');
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from te where c2=1 and (c1,c3) > (1,1);
+----+-------------+-------+------+---------------+---------+---------+-------+-
-----+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   |
rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+-
-----+-------------+
|  1 | SIMPLE      | te    | ref  | PRIMARY       | PRIMARY | 4       | const |
   3 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+-
-----+-------------+
1 row in set (0.11 sec)

Note that only first column form the PRIMARY KEY is used. While semantically equivalent query is optimized way better:

mysql> explain select * from te where (c2=1) and (c1 > 1 or ((c1=1) and (c3>1)))
;
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  |
rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
|  1 | SIMPLE      | te    | range | PRIMARY       | PRIMARY | 12      | NULL |
   3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
1 row in set (0.00 sec)

Suggested fix:
Explain all current limitations of optimizer with regard to queries with row constructors somewhere. Document workarounds like the above.

Hello Valeriy,

Thank you for the report.
Verified as described.

Thanks,
Umesh

Valeriy,

This is a fully valid documentation request. Also a valid feature request for the optimizer. I have only one small quibble with your example.

In the expression (c1,c2) > (1,1), both columns should be greater then 1.

Sinisa,

The expression "(c1, c2) > (1, 1)" is equivalent to "(c1 = 1 AND c2 > 1) OR (c1 > 1)".

In fact, that's most of the reason for this feature's existence.

Jeremy, Valeriy,

We have run a number of tests and our server behaves properly, in the way that you described. However, this logic is not documented properly at all, which makes this bug fully justified !!!

Posted by developer:
 
New section:

http://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html

The title of the new documentation page says "optimization", implying that row constructor operations might be optimized.  However, a hint of the truth is buried in the text: "rewriting the row constructor expression using an equivalent nonconstructor expression may result in more complete index use".

To optimize row constructor inequalities you have always (even as recently as 8.0.15) needed to rewrite the query using ANDs and ORs.

Please make this clearer in the documentation _and_ think about improving the Optimizer.