Re: GROUP BY optimization headscratcher
Reaching on this one, but how about reversing your join: FROM T2,T1 Based on your query, it appears you are summarizing all the data in T2 and pulling some extra data from T1. Since you are doing a full table scan (using all the records) on T2, you probably want to center your query around that table. You could even try a left join (T2 left join T1 on T2.qid=T1.qid). Also, create another index on the guid column in T2. That should allow MySQL to use the index to sort the records by guid for grouping. Finally, make sure your sort_buffer setting is high enough to accommodate what you are trying to do. On Aug 14, 2004, at 5:17 PM, Matt Eaton wrote: Michael, and . However, it didn't work. Whole thing still takes about 1 second. On the other hand, I realized I'm an idiot and that the reason it was running so fast when I handled temporary tables myself is that I was using mysqlcc, which truncated the first table to 1000 rows rather than 475,000, which--as one would imagine--sped things up considerably. However, I'm still looking for a way to make this fast. This is an integral part of my application, it'd be a big load off my mind ( my processesor) if I could get it under half a second on my box. I've made the changes Michael suggested, so I was wondering if anyone had suggestions on how to optimize this further. Below please find the query in question, a little background, the create statements and the output of explain: SELECT T2.guid, sum(T2.d+T1.d) AS theSum FROM T1, T2 WHERE T1.qid=T2.qid GROUP BY T2.guid; (I grouped by the wrong T last time, sorry). T1 contains one user, and their answers to various questions, so guid actually has only 1 value in this table, and qid has about 65, for a total of 65 rows. T2 contains about 15,000 users, so guid has 15,000 different values and qid has 34 possible values, and the total cardinality comes out to around 475,000. The Create Table statements look like: CREATE TABLE `T1` ( `guid` smallint(5) unsigned NOT NULL default '0', `qid` smallint(5) unsigned NOT NULL default '0', `a` tinyint(2) NOT NULL default '-2', `d` tinyint(2) NOT NULL default '-2', UNIQUE KEY `IX_T1_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM CREATE TABLE `T2` ( `guid` mediumint(8) unsigned NOT NULL default '0', `qid` tinyint(3) unsigned NOT NULL default '0', `a` tinyint(4) NOT NULL default '0', `d` decimal(1,0) unsigned NOT NULL default '0', UNIQUE KEY `IX_T2_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM And the explain is: +---+--+++-+ +--+-+ | table | type | possible_keys | key| key_len | ref| rows | Extra | +---+--+++-+ +--+-+ | T1| ALL | IX_T1_qid_guid | NULL |NULL | NULL | 65 | Using temporary; Using filesort | | T2| ref | IX_T2_qid_guid | IX_T2_qid_guid | 1 | T1.qid | 4979 | Using where | +---+--+++-+ +--+-+ Thanks so much! -Matt -Original Message- From: [mailto:[EMAIL PROTECTED] Sent: Saturday, August 14, 2004 3:46 AM To: Matt Eaton Subject: Re: GROUP BY optimization headscratcher Matt ME CREATE TABLE `T1` ( ME `guid` smallint(5) unsigned NOT NULL default '0', ME `qid` smallint(5) unsigned NOT NULL default '0', ME `a` tinyint(2) NOT NULL default '-2', ME `d` tinyint(2) NOT NULL default '-2', ME KEY `IX_FW_qid` (`qid`), ME KEY `IX_FW_d` (`d`) ME ) TYPE=HEAP ME CREATE TABLE `T2` ( ME `guid` mediumint(8) unsigned NOT NULL default '0', ME `qid` tinyint(3) unsigned NOT NULL default '0', ME `a` tinyint(4) NOT NULL default '0', ME `d` decimal(1,0) unsigned NOT NULL default '0', ME PRIMARY KEY (`guid`,`qid`), ME KEY `IX_s23aw_d` (`d`), ME KEY `IX_s23aw_qid` (`qid`) ME ) TYPE=HEAP ME SELECT T1.guid, sum(T1.d + T2.d) as theSum ME FROM T1, T2 ME WHERE T1.qid=T2.qid ME GROUP BY T1.guid make key in T1: KEY `` (qid,guid ) and change table type to MyIsam for both table. Michael Monashev http://softsearch.ru/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY optimization headscratcher
Matt ME CREATE TABLE `T1` ( ME `guid` smallint(5) unsigned NOT NULL default '0', ME `qid` smallint(5) unsigned NOT NULL default '0', ME `a` tinyint(2) NOT NULL default '-2', ME `d` tinyint(2) NOT NULL default '-2', ME KEY `IX_FW_qid` (`qid`), ME KEY `IX_FW_d` (`d`) ME ) TYPE=HEAP ME CREATE TABLE `T2` ( ME `guid` mediumint(8) unsigned NOT NULL default '0', ME `qid` tinyint(3) unsigned NOT NULL default '0', ME `a` tinyint(4) NOT NULL default '0', ME `d` decimal(1,0) unsigned NOT NULL default '0', ME PRIMARY KEY (`guid`,`qid`), ME KEY `IX_s23aw_d` (`d`), ME KEY `IX_s23aw_qid` (`qid`) ME ) TYPE=HEAP ME SELECT T1.guid, sum(T1.d + T2.d) as theSum ME FROM T1, T2 ME WHERE T1.qid=T2.qid ME GROUP BY T1.guid make key in T1: KEY `` (qid,guid ) and change table type to MyIsam for both table. Michael Monashev http://softsearch.ru/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GROUP BY optimization headscratcher
Michael, and . However, it didn't work. Whole thing still takes about 1 second. On the other hand, I realized I'm an idiot and that the reason it was running so fast when I handled temporary tables myself is that I was using mysqlcc, which truncated the first table to 1000 rows rather than 475,000, which--as one would imagine--sped things up considerably. However, I'm still looking for a way to make this fast. This is an integral part of my application, it'd be a big load off my mind ( my processesor) if I could get it under half a second on my box. I've made the changes Michael suggested, so I was wondering if anyone had suggestions on how to optimize this further. Below please find the query in question, a little background, the create statements and the output of explain: SELECT T2.guid, sum(T2.d+T1.d) AS theSum FROM T1, T2 WHERE T1.qid=T2.qid GROUP BY T2.guid; (I grouped by the wrong T last time, sorry). T1 contains one user, and their answers to various questions, so guid actually has only 1 value in this table, and qid has about 65, for a total of 65 rows. T2 contains about 15,000 users, so guid has 15,000 different values and qid has 34 possible values, and the total cardinality comes out to around 475,000. The Create Table statements look like: CREATE TABLE `T1` ( `guid` smallint(5) unsigned NOT NULL default '0', `qid` smallint(5) unsigned NOT NULL default '0', `a` tinyint(2) NOT NULL default '-2', `d` tinyint(2) NOT NULL default '-2', UNIQUE KEY `IX_T1_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM CREATE TABLE `T2` ( `guid` mediumint(8) unsigned NOT NULL default '0', `qid` tinyint(3) unsigned NOT NULL default '0', `a` tinyint(4) NOT NULL default '0', `d` decimal(1,0) unsigned NOT NULL default '0', UNIQUE KEY `IX_T2_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM And the explain is: +---+--+++-++--+-+ | table | type | possible_keys | key| key_len | ref| rows | Extra | +---+--+++-++--+-+ | T1| ALL | IX_T1_qid_guid | NULL |NULL | NULL | 65 | Using temporary; Using filesort | | T2| ref | IX_T2_qid_guid | IX_T2_qid_guid | 1 | T1.qid | 4979 | Using where | +---+--+++-++--+-+ Thanks so much! -Matt -Original Message- From: [mailto:[EMAIL PROTECTED] Sent: Saturday, August 14, 2004 3:46 AM To: Matt Eaton Subject: Re: GROUP BY optimization headscratcher Matt ME CREATE TABLE `T1` ( ME `guid` smallint(5) unsigned NOT NULL default '0', ME `qid` smallint(5) unsigned NOT NULL default '0', ME `a` tinyint(2) NOT NULL default '-2', ME `d` tinyint(2) NOT NULL default '-2', ME KEY `IX_FW_qid` (`qid`), ME KEY `IX_FW_d` (`d`) ME ) TYPE=HEAP ME CREATE TABLE `T2` ( ME `guid` mediumint(8) unsigned NOT NULL default '0', ME `qid` tinyint(3) unsigned NOT NULL default '0', ME `a` tinyint(4) NOT NULL default '0', ME `d` decimal(1,0) unsigned NOT NULL default '0', ME PRIMARY KEY (`guid`,`qid`), ME KEY `IX_s23aw_d` (`d`), ME KEY `IX_s23aw_qid` (`qid`) ME ) TYPE=HEAP ME SELECT T1.guid, sum(T1.d + T2.d) as theSum ME FROM T1, T2 ME WHERE T1.qid=T2.qid ME GROUP BY T1.guid make key in T1: KEY `` (qid,guid ) and change table type to MyIsam for both table. Michael Monashev http://softsearch.ru/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
