Using Tuple Comparison In Mysql Is It Efficient?

I have a table of books : CREATE TABLE `books` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `nameOfBook` VARCHAR(32), `releaseDate` DATETIME NULL DEFAULT NULL, PRIMARY K

Solution 1:

I've been irritated by this for years. WHERE (a,b) > (1,2) has never been optimized, in spite of it being easily transformed into the other formulation. Even the other format was poorly optimized until a few years ago.

Using EXPLAIN FORMAT=JSON SELECT ... might give you some better clues.

Meanwhile, EXPLAIN ignored the LIMIT and suggested 1015876. On many cases, EXPLAIN provides a "decent" Row estimate, but not either of these.

Feel free to file a bug report: http://bugs.mysql.com (and post the link here).

Another formulation was recently optimized, in spite of OR being historically un-optimizable.

where  releaseDate <  '2016-11-07'  
   OR (releaseDate  = '2016-11-07' AND id < 3338191)  

For measuring query optimizations, I like to do:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE'Handler%';

Small values, such as '50' for your case, indicate good optimization; large value (1M) indicate a scan. The Handler numbers are exact; unlike the estimates in EXPLAIN.

Update5.7.3 has improved handling of tuples, aka "row constructors"

UpdateMySQL Bug#104128 covers this.