Welcome to WebmasterWorld Guest from 54.144.107.83

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)

Junior Member

10+ Year Member

joined:Apr 26, 2004
posts: 137
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5550
votes: 24


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

Junior Member

10+ Year Member

joined:Apr 26, 2004
posts:137
votes: 0


Yes.

See the create table statement above.

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

Moderator from US 

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

joined:Dec 10, 2005
posts:5550
votes: 24


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members