Hi, Surely, the having clause is not redundant ( I misread it as > 0).
Is it not the compound index (on mot,date,numresponse) which would make the query fast instead of three single column indexes? Thanks, Anvar. At 01:40 PM 19/01/2002 -0500, you wrote: >Anvar had some very good explanations about the time it takes to run the >queries. > >##Here are some work arounds: >##If you need to have these columns (mot, date, numresponse) in the group by >clause, >##try putting an index on each of them to speed it up. > >mysql>>alter table searchhardwarefr3 > >>add index idx_mot(mot); > >##... etc. > >##this should speed up the 1st query for sure. >##if the second query is still slow, (i'm not sure about the exact details >of mysql, so this might or might not make a difference) > >## put the results from your first query into a temporary table (mytemp): >mysql>>create temporary table mytemp > >> SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 > >>GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; >## then explicitly index both columns >mysql >>alter table mytemp > >> add index idx_count(count); >mysql >>alter table mytemp > >> add index idx_numresponse(numresponse) >##and then run the following query > >mysql>>select distinct count, numresponse from temp (mytemp) > >##by the way, I don't think the HAVING clause is redundant. > > >Good Luck. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
