A perfect interpretation of the stored procedure of MySQL 5.6 MRR

The stored procedure of MySQL 5.6 MRR perfectly interprets the upcoming release of MySQL 5.6, and 5.6 has made many optimizations to the optimizer. This time I mainly explain MRR (MULTI-RANGE-READ ). I used stored procedures to explain the changes in this process. Let's take a look. For statements: [SQL] select log_time from person where nick_name = 'Lucy '; TABLE structure: [SQL] CREATE TABLE 'person' ('id' int (10) unsigned not null AUTO_INCREMENT, 'Nick _ name' varchar (40) not null, 'Log _ time' timestamp not null default CURRENT_TIMESTAMP, primary key ('id '), www.2cto.com KEY 'idx _ nick_name '('Nick _ name') ENGINE = InnoDB AUTO_INCREMENT = 5 default charset = latin1 first MySQL 5.5. [SQL] DELIMITER $ USE 'ytt' $ DROP PROCEDURE IF EXISTS 'SP _ range_scan5_5 '$ CREATE DEFINER = 'admin' @' % 'Procedure 'sp _ range_scan5_5 '() BEGIN -- Sample SQL statement is below. -- select log_time from person where nick_name = 'Lucy '; DECLARE I INT UNSIGNED DEFAULT 0; DECLARE cnt INT UNSIGNED DEFAULT 0; SET @ result = ''; SELECT COUNT (1) INTO cnt FROM person WHERE nick_name = 'Lucy '; loop1: WHILE I <cnt do set @ stmt = CONCAT ('select id into @ v_id from person where nick_name = ''lucy ''order by nick_name asc limit ', I ,', 1 '); PREPARE s1 FROM @ stmt; EXECUTE s1; SET @ result = CONCAT (@ result, 'select log_time from person where id = @ v_id '); SET @ result = CONCAT (@ result, 'Union all'); SET I = I + 1; END WHILE loop1; SET @ result = SUBSTR (@ result, 1, CHAR_LENGTH (@ result)-CHAR_LENGTH ('Union all'); www.2cto.com PREPARE s1 FROM @ result; EXECUTE s1; drop prepare s1; SET @ result = NULL; END $ DELIMITER; mySQL 5.6. [SQL] DELIMITER $ USE 'ytt' $ DROP PROCEDURE IF EXISTS 'SP _ range_scan5_6 '$ CREATE DEFINER = 'admin' @' % 'Procedure 'SP _ range_scan5_6 '() BEGIN -- Sample SQL statement is below. -- select log_time from person where nick_name = 'Lucy '; DECLARE I INT UNSIGNED DEFAULT 0; DECLARE cnt INT UNSIGNED DEFAULT 0; DECLARE ids TEXT; SET ids = ''; select count (1) INTO cnt FROM person WHERE nick_name = 'Lucy '; loop1: WHILE I <cnt do set @ stmt = CONCAT ('select id into @ v_id from person where nick_name = ''lucy ''www.2cto.com order by nick_name asc limit ', I ,', 1 '); PREPARE s1 FROM @ stmt; EXECUTE s1; SET ids = CONCAT (ids, @ v_id,', '); SET I = I + 1; END WHILE loop1; SET ids = CONCAT (', SUBSTR (ids, 1, CHAR_LENGTH (ids)-1 ),')'); SET @ result = CONCAT ('select log_time from person where id in', ids); PREPARE s1 FROM @ result; EXECUTE s1; drop prepare s1; SET @ result = NULL; END $ DELIMITER;