Yes, good keys are crucial but I try to avoid them when demonstrating temp tables (I think it blurs the issue). All you need to do to have keys on a temp table is to either add the KEY() definitions to your CREATE TEMPORARY TABLE statements like this: CREATE TEMPORARY TABLE tmpCount1(KEY(name)) SELECT name, count(1) fq from table1 WHERE (conditions) GROUP BY name;
CREATE TEMPORARY TABLE tmpCount2(KEY(name)) SELECT name, count(1) fq from table2 WHERE (conditions) GROUP BY name; (notice that I use one of the column names that will result from the SELECT clause. I could have also said "KEY(fq)" as fq is one of the columns created in the new table) OR add them to the tables after you create them like this: ALTER TABLE tmpCount1 ADD KEY(name); ALTER TABLE tmpCount2 ADD KEY(name); Either way you wind up with indexed temporary tables. Your queries should fly, now. I'm just glad I could help!! 8-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Søren Ragsdale <[EMAIL PROTECTED]> wrote on 10/26/2004 05:38:21 PM: > I'm wondering about something: the TEMPORARY TABLEs we're generating > here do not have any keys or indexes on them. My EXPLAIN is > complaining that it can't use any keys in the INNER JOIN. Is this a > problem? Should I be creating TEMPORARY TABLES with keys? > > Thanks very much for your help - I've finally gotten the hang of > temporary tables and they're a big help. > > On Oct 12, 2004, at 10:34 AM, [EMAIL PROTECTED] wrote: > > > Good, you recognize the need to perform two separate aggregates (GROUP > > BYs) and compare the separate results... In fact, you may need two > > temp > > tables so that you can (INNER / LEFT) JOIN one to the other. (Yes, I > > can > > think of a query that works without the second temp table but I think > > it's > > kludgey and would rather not post it.) > > > > To find same name and count > > > > CREATE TEMPORARY TABLE tmpCount1 > > SELECT name, count(1) fq > > from table1 > > WHERE (conditions) > > GROUP BY name; > > > > CREATE TEMPORARY TABLE tmpCount2 > > SELECT name, count(1) fq > > from table2 > > WHERE (conditions) > > GROUP BY name; > > > > SELECT t1.name > > FROM tmpCount1 t1 > > INNER JOIN tmpCount2 t2 > > on t1.name = t2.name > > and t1.fq = t2.fq; > > > > DROP TEMPORARY TABLE tmpCount1, tmpCount2; > > > > > > To find where table2 has MORE than table1 > > > > SELECT t2.name > > FROM tmpCount2 t2 > > INNER JOIN tmpCount1 t1 > > on t1.name = t2.name > > WHERE t1.fq < t2.fq; > > > > Does this help in a general way? > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/12/2004 01:18:39 > > PM: > > > >> Quoting [EMAIL PROTECTED]: > >> > >>>> Why would you want to do that? bananacount is something you can > >>>> calculate with a LEFT JOIN and a GROUP BY, so storing it in the DB > >>>> would break normalization. > >>> > >>> This would also the first step in creating your own OLAP cube. For > > each > >>> statistic, you save yourself a lot of time if you compute the results > > of > >>> several GROUP BY functions (SUM, AVG, STD, etc) and store those > > results > >>> into intermediate tables. Then when you want to start slicing and > > dicing > >>> your data, you do more retrieval and less computations. This > >>> seriously > >>> improves the performance of your reports. Each new record inserted > > into > >>> your cube would require you to update every computed statistics table > > that > >>> covered your new record. But that *is* the trade off, isn't it? Much > >>> faster analysis for much slower transaction performance. Thus the > > basic > >>> difference between OLAP and OLTP. > >> > >> This is closer to the reason why I'm trying to do this. Obviously > >> this > > is an > >> example: I'm not actually tracking real monkeys and real banannas with > > mysql. > >> (Sorry if this disappoints anyone!) In my actual application I'm > >> doing > > a JOIN > >> between this table and another one with a GROUP BY (a very > > differentcriteria) > >> and a HAVING clause which selects only members from this other > > tablewhich have > >> as many elements in this second grouping as appeared in the first > > grouping. > >> > >> So basically I'm trying to compare the results of two different GROUP > >> BY > >> statements with a HAVING clause and I'm pretty sure I can't do it all > > within > >> one statement, so I'm caching the results of one of the GROUPs as a > > value in > >> one of the tables. > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > >> http://lists.mysql.com/[EMAIL PROTECTED] > >> >
