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

[23 Oct 2013 15:42] Valeriy Kravchuk
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.
[23 Oct 2013 16:30] MySQL Verification Team
Hello Valeriy,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[24 Oct 2013 15:57] MySQL Verification Team
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.
[24 Oct 2013 20:15] Jeremy Cole
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.
[2 Dec 2013 14:28] MySQL Verification Team
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 !!!
[21 Mar 2016 16:04] Paul Dubois
Posted by developer:
 
New section:

http://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html
[14 Jul 2019 22:57] Rick James
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.