List:General Discussion« Previous MessageNext Message »
From:Matt Eaton Date:August 14 2004 9:17pm
Subject:RE: GROUP BY optimization headscratcher
View as plain text  
Здравствуйте 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:michael@stripped] 
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 `zzzz` (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/mysql?unsub=1

Thread
GROUP BY optimization headscratcherMatt Eaton14 Aug
  • Re: GROUP BY optimization headscratcherМихаил Монашёв14 Aug
    • RE: GROUP BY optimization headscratcherMatt Eaton14 Aug
      • Re: GROUP BY optimization headscratcherBrent Baisley16 Aug