Hi, In fact I have a compound index on (mot,date,numreponse,topic), so I assume (mot,date,numreponse) is also optimised if I correctly understand how indexs work ;) Anyway, I just wanted to run this query once, so it's not really my priority to make speed faster on this particular query (moreover putting a specific index on a table with more than 2M table will take a big amount of time, probably as long as my query without DISTINCT ;))
Jocelyn ----- Original Message ----- From: "Anvar Hussain K.M." <[EMAIL PROTECTED]> To: "Kalok Lo" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, January 21, 2002 5:57 AM Subject: Re: Why does DISTINCT take so long time ?? > 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 > > --------------------------------------------------------------------- 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
