ÐÐÑÐÐÑÑÐÑÐÑÐ 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 `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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
