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
EmoticonEmoticon