Welcome to WebmasterWorld Guest from 50.16.78.128

Forum Moderators: open

Message Too Old, No Replies

Help with Optimizing this simple SQL SELECT - Too many rows?

Order By, Explain Select,limit 1

   
11:40 am on Nov 10, 2008 (gmt 0)

10+ Year Member



Hello,

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.

4:37 pm on Nov 10, 2008 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Do you have an index on the image_seq field?
5:13 pm on Nov 10, 2008 (gmt 0)

10+ Year Member



Yes.

See the create table statement above.

5:33 pm on Nov 10, 2008 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



You have it set as a key, not an index.

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.