Hi all. Got a weird one. Mysql 4.0.20. Let's say for the sake of argument that I've got two tables, T1 & T2. Here are the two create statements: 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', KEY `IX_FW_qid` (`qid`), KEY `IX_FW_d` (`d`) ) TYPE=HEAP 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', PRIMARY KEY (`guid`,`qid`), KEY `IX_s23aw_d` (`d`), KEY `IX_s23aw_qid` (`qid`) ) TYPE=HEAP So, in T1 there are as many records as there are qids (around 150) (there's only one user in T1). In T2 there are as many records as there are qids * user id's = (around 497,964). The weirdness comes when I try to join them and do a group by at the same time. The following: SELECT T1.guid, sum(T1.d + T2.d) as theSum FROM T1, T2 WHERE T1.qid=T2.qid GROUP BY T1.guid takes 1 second to run. This seems absurdly long. Explain shows that everything seems fine (although it shows that T2 has a key length of 2, which is weird, and I don't really understand), and if I do the same query without the group by or the sum, it goes in 0.01 seconds. So, I ran the same query without the group by and the sum and stored the result set in a temporary table, and then did a group by on guid for that temporary table, and that ran 0.01 seconds. so I've got a solution to my problem already, even though it's a two-query solution. However, I'm really curious as to why MySQL takes so long on my original query. It seems like it's creating a temporary table in memory and doing the group by on that, which is exactly what I was doing the second time around. and I find it hard to believe that I'm that much smarter than the MySQL preprocessor. So, if anyone has any thoughts on this strange disparity in time, I'd be interested to hear them! Thanks a lot! -Matt
