MySQL Forums
Forum List  »  General

Optimising a GROUP BY and ORDER BY in a joined query
Posted by: Michael Ditum
Date: March 29, 2010 08:03AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.