Tutorial :Slow query, can I speed it up?



Question:

I'm retrieving images stored as blobs in the database using a python script running on the same server.

   SELECT *        FROM imagedb_production.imagedb IMAGE   LEFT JOIN dccms_production.tblmedia MEDIA ON IMAGE.name = MEDIA.name   LEFT JOIN dccms_production.tblmultimedia CAP ON MEDIA.contentItemID = CAP.contentItemID       LIMIT 5000,100;  

An EXPLAIN returns

id  select_type table   type    possible_keys               key         key_len     ref                                     rows    Extra  1   SIMPLE      IMAGE   index   NULL                        name_idx        767     NULL                                    10145962        Using index  1   SIMPLE      MEDIA   ref     name                        name            63      imagedb_production.IMAGE.name           1     1   SIMPLE      CAP     eq_ref  PRIMARY,idx_contentItemID   PRIMARY         4       dccms_production.MEDIA.contentItemID    1       Using index  

(Sorry the output looks like crap)

This query takes close to 12 minutes is there any way I can speed this up before going through and tuning the mysql db instance?

Additional information

'imagedb', 'CREATE TABLE `imagedb` (    `multimediaID` int(11) NOT NULL auto_increment,    `name` varchar(255) NOT NULL,    `content` mediumblob,    `description` longtext,    `mime_type` varchar(255) default NULL,    PRIMARY KEY  (`multimediaID`),    KEY `name_idx` (`name`)  ) ENGINE=InnoDB AUTO_INCREMENT=2320759 DEFAULT CHARSET=utf8'      'tblmedia', 'CREATE TABLE `tblmedia` (    `mediaID` int(11) NOT NULL auto_increment,    `contentItemID` int(11) NOT NULL default ''0'',    `name` varchar(255) default NULL,    `width` int(11) default NULL,    `height` int(11) default NULL,    `file1Size` bigint(20) default NULL,    `file2Size` bigint(20) default NULL,    `mediaSlug` int(11) default NULL,    PRIMARY KEY  (`mediaID`),    KEY `idx_contentItemID` (`contentItemID`),    KEY `name` (`name`(20))  ) ENGINE=InnoDB AUTO_INCREMENT=899975 DEFAULT CHARSET=utf8'    'tblmultimedia', 'CREATE TABLE `tblmultimedia` (    `contentItemID` int(11) NOT NULL default ''0'',    `caption` text,    `mimeType` varchar(255) default NULL,    PRIMARY KEY  (`contentItemID`),    KEY `idx_contentItemID` (`contentItemID`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8'  


Solution:1

You have 10,000,000 rows with no sorting, I would fix that. Add a default order by clause.

Older versions of MySQL did not take limit clauses into account until much later. I think newer versions do a better job of that. You might want to check into different ways to limit the result set.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »