homepage Welcome to WebmasterWorld Guest from 54.198.140.148
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Help with Optimizing this simple SQL SELECT - Too many rows?
Order By, Explain Select,limit 1
jezzer300




msg:3783374
 11:40 am on Nov 10, 2008 (gmt 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.

 

LifeinAsia




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

Do you have an index on the image_seq field?

jezzer300




msg:3783555
 5:13 pm on Nov 10, 2008 (gmt 0)

Yes.

See the create table statement above.

LifeinAsia




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

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved