Hi,
I'm attempting to optimise a query and having some problems when I had ordering and grouping to it.
Here's the create table syntax for the 2 tables..
CREATE TABLE `vod_control` (
`VC_AssetId` varchar(200) collate utf8_unicode_ci NOT NULL,
`VC_Price` float NOT NULL,
`VC_Start` date NOT NULL,
`VC_End` date NOT NULL,
`VC_Metric` tinyint(11) unsigned NOT NULL default '128',
`VC_Active` tinyint(1) NOT NULL,
`VC_Access` enum('public','private','sharable') collate utf8_unicode_ci NOT NULL,
`VC_Owner` varchar(100) collate utf8_unicode_ci NOT NULL,
`VC_Valid` tinyint(1) NOT NULL,
`VC_FilePath` varchar(4096) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`VC_AssetId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `vod_package` (
`VP_AssetId` varchar(200) collate utf8_unicode_ci NOT NULL,
`VP_Parent` varchar(200) collate utf8_unicode_ci NOT NULL,
`VP_Family` varchar(100) collate utf8_unicode_ci NOT NULL,
`VP_Type` enum('Video','VideoBookmark','Audio','AudioBookmark') collate utf8_unicode_ci NOT NULL,
`VP_FilePath` varchar(4096) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`VP_AssetId`),
KEY `VP_FamilyIndex` (`VP_Family`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
and here's all the data needed to recreate the problem.
INSERT INTO `vod_control` VALUES ('Asset1',3.5,'0000-00-00','0000-00-00',128,1,'public','mike',1,'/path/to/file');
INSERT INTO `vod_control` VALUES ('Asset2',0,'0000-00-00','0000-00-00',128,1,'public','mike',1,' /path/to/file2');
INSERT INTO `vod_control` VALUES ('Asset3',0,'0000-00-00','0000-00-00',128,1,'public','mike',1,' /path/to/file3');
INSERT INTO `vod_package` VALUES ('Asset1','','baa','Video','/path/to/other/file');
INSERT INTO `vod_package` VALUES ('Asset2','','foo','Video','/path/to/other/file2');
INSERT INTO `vod_package` VALUES ('Asset3','','foo','Video','/path/to/other/file3');
The query I am doing is trying to a get a list of the families that are in valid packages.
SELECT vod_package.VP_Family FROM vod_control
JOIN vod_package ON VC_AssetId = VP_AssetId
WHERE VC_Valid = 1
GROUP BY VP_Family
ORDER BY VP_Family
LIMIT 100;
This query works correctly but as I add more entries it gets slower and slower. Once I have 10,000 entries in each table it takes "00:00:00:999" to complete. If I remove the GROUP BY and the ORDER BY lines it drops to "00:00:00:004".
Doing an explain on the output gets me...
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------------+-------+---------------------------------+
| 1 | SIMPLE | vod_package | ALL | PRIMARY | NULL | NULL | NULL | 10819 | Using temporary; Using filesort |
| 1 | SIMPLE | vod_control | eq_ref | PRIMARY | PRIMARY | 602 | tpdata.vod_package.VP_AssetId | 1 | Using where |
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------------+-------+---------------------------------+
From what I've read the slowdown is due to the "Using filesort" and "Using temporary" in the extra field. I've tried adding an index in for VP_Family but it hasn't sped it up at all.
Am I right in the cause of the slow down and is there anything I can do about it?
Thanks in advance for any help!
Mike