the storage engine for rental tables is innodb, rental_ myisam tables, and the storage engine for Myisam tables is myisam. The structure of the two tables is the same except for different storage engines.
CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rental_myisam` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;
using explain analysis, it is found that rental table uses primary key index sorting, while rental_ myisam table does not use primary key index sorting, but uses "file sorting" in extra. I don"t quite understand why there is such a difference.
explain select * from rental where return_date > "2005-08-30" order by rental_idG
explain select * from rental_myisam where return_date > "2005-08-30" order by rental_idG
