MySQL Forums :: General :: Optimising a GROUP BY and ORDER BY in a joined query


Advanced Search

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


Subject Written By Posted
Optimising a GROUP BY and ORDER BY in a joined query Michael Ditum 03/29/2010 08:03AM
Re: Optimising a GROUP BY and ORDER BY in a joined query Rick James 03/30/2010 10:43PM
Re: Optimising a GROUP BY and ORDER BY in a joined query Michael Ditum 03/31/2010 03:29AM
Re: Optimising a GROUP BY and ORDER BY in a joined query Rick James 03/31/2010 11:18PM
Re: Optimising a GROUP BY and ORDER BY in a joined query Michael Ditum 04/01/2010 11:17AM
Re: Optimising a GROUP BY and ORDER BY in a joined query Rick James 04/01/2010 07:32PM
Re: Optimising a GROUP BY and ORDER BY in a joined query Peter Brawley 04/02/2010 11:34AM


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.