Welcome to WebmasterWorld Guest from 220.127.116.11
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
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
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.
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.