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.
----- Original Message -----
From: "Anvar Hussain K.M." <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, January 19, 2002 2:27 AM
Subject: Re: Why does DISTINCT take so long time ??
> Hi,
>
> Yes, the reason for the time difference is that for distinct query, as
> Sinisa noted, it has to reiterate.
>
> For the output to generate, first the rows have to be ordered ( in this
> case since count(*) is given every column
> should be present in the comparison.) using a temp table (or any other
> mechanism to keep rows ordered).
>
> For the first query also there should be an intermediate temp table to
> order rows but here it is only necessary to
> consider the columns in the group by clause.
>
> Considering these, a rough and primitive estimate of the time taken to
> execute the query can be found.
>
> Suppose the average length of a row is 300 bytes and the three columns in
> the group by clause takes
> 30 bytes average. Then if the first query takes 15 minutes, the second
> query will take 150 minutes.
>
>
> This may not be the real scenario with mysql but some thing similar.
> The having clause I feel, is redundant. the 15 min for the first qurey
> seem too much, perhaps indexing might
> help.
>
> Hope somebody else has a better explanation.
> Anvar.
>
> At 02:41 AM 18/01/2002 +0100, you wrote:
> >Hi,
> >
> >I've notice sometimes DISTINCT clause take a really high amount of time
to
> >remove duplicates whereas it should be really quick (I assume it should
be
> >;))
> >
> >My first query is :
> >
> >mysql> SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP
BY
> >mot,date,numreponse HAVING count>1 LIMIT 100;
> >
> >it returns :
> >
> >+-------+------------+
> >| count | numreponse |
> >+-------+------------+
> >| 2 | 111239 |
> >| 2 | 108183 |
> >| 2 | 111173 |
> >| 2 | 111383 |
> ><cut>
> >| 2 | 111239 |
> >| 2 | 111760 |
> >| 3 | 109166 |
> >| 2 | 111109 |
> >| 3 | 109166 |
> >+-------+------------+
> >58 rows in set (14 min 51.15 sec)
> >
> >My second query is :
> >
> >
> >mysql> SELECT DISTINCT COUNT(*) as count, numreponse FROM
searchhardwarefr3
> >GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100;
> >
> >Well I'm not enough patient to wait, but when I stop the querie, it has
been
> >running for more than 3500 seconds... (and more than 45mn in 'Removing
> >duplicates' state...)
> >
> >mysql> EXPLAIN SELECT DISTINCT COUNT(*) as count, numreponse FROM
> >searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100;
>
>+-------------------+-------+---------------+---------+---------+------+---
-
> >-----+------------------------------+
> >| table | type | possible_keys | key | key_len | ref |
> >rows | Extra |
>
>+-------------------+-------+---------------+---------+---------+------+---
-
> >-----+------------------------------+
> >| searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL |
> >2026032 | Using index; Using temporary |
>
>+-------------------+-------+---------------+---------+---------+------+---
-
> >-----+------------------------------+
> >1 row in set (0.00 sec)
> >
> >mysql> EXPLAIN SELECT COUNT(*) as count, numreponse FROM
searchhardwarefr3
> >GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100;
>
>+-------------------+-------+---------------+---------+---------+------+---
-
> >-----+------------------------------+
> >| table | type | possible_keys | key | key_len | ref |
> >rows | Extra |
>
>+-------------------+-------+---------------+---------+---------+------+---
-
> >-----+------------------------------+
> >| searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL |
> >2026032 | Using index; Using temporary |
>
>+-------------------+-------+---------------+---------+---------+------+---
-
> >-----+------------------------------+
> >1 row in set (0.00 sec)
> >
> >
> >Why does it take so much time to remove duplicates in only 58 rows ??
> >
> >Thank you :)
> >
> >Regards,
> >
> >Jocelyn Fournier
> >Presence-PC
> >
> >
> >
> >
> >
> >
> >---------------------------------------------------------------------
> >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
> ><[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
<[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
