Forum Moderators: open
I'm optimizing a MySQL 5.1 Query and my EXPLAIN SELECT tells me 4 rows were used in the below query when I would expect only 1 row to be used. (1264 rows in this table)
It's true that without LIMIT 1, 4 rows would be returned.
Why does MySQL need to view all 4 rows when using "ORDER BY image_seq":
- I'm reading using the 1st 3 parts of the primary key
- The 4th part of the primary key is where I attempt to use "ORDER BY" to quickly get to the 1 row with my LIMIT 1
The Select:
SELECT filename,description,visable FROM bct_image WHERE image_type='I' AND id='SBPETER6182' AND site_code='bc' ORDER BY image_seq ASC LIMIT 1
The table:
CREATE TABLE `bct_image` (
`image_type` char(1) NOT NULL,
`id` varchar(20) NOT NULL,
`site_code` char(2) NOT NULL ,
`image_seq` int(11) NOT NULL auto_increment,
`filename` varchar(100) NOT NULL ,
`description` varchar(150) NOT NULL COMMENT ,
`visable` char(1) NOT NULL ,
PRIMARY KEY (`site_code`,`image_type`,`id`,`image_seq`),
KEY `image_seq` (`image_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=1639 DEFAULT CHARSET=latin1
The 4 rows (returned without a LIMIT 1):
INSERT INTO `bct_image` (`image_type`, `id`, `site_code`, `image_seq`, `filename`, `description`, `visable`) VALUES
('I', 'SBPETER6182', 'bc', 931, 'SBPETER6182_0005.JPG', '', 'Y'),
('I', 'SBPETER6182', 'bc', 1510, 'SBPETER6182_0006.JPG', '', 'Y'),
('I', 'SBPETER6182', 'bc', 1511, 'SBPETER6182_0001.jpg', '', 'Y'),
('I', 'SBPETER6182', 'bc', 1512, 'SBPETER6182_0003.JPG', '', 'Y');
Obviously creating a test with only 4 rows may result in 4 reads anyway due to the small table. My data has 1264 rows.
See the create table statement above.
I'm not sure about the underlying structure of MySQL 5.1, but my guess is that since image_seq is not indexed, it has to get all the rows meeting the criteria, then process the ORDER BY. If image_seq has an index, it MAY not have to read in all the matching rows.
Try it and see.
