List:MySQL on Win32« Previous MessageNext Message »
From:Joelle Tegwen Date:April 14 2006 2:20pm
Subject:Re: group slow
View as plain text  
Just looking at your table structure I would recommend first optimizing 
your table. I found 
http://db4free.blogspot.com/2006/03/procedure-analyse-option.html to be 
very helpful in learning how to optimized data types for tables.

Also http://dev.mysql.com/doc/refman/5.0/en/query-speed.html talks about 
optimization in general.

Looking at the query, I would to SELECT username, count(username) as 
usernum FROM table GROUP BY username;

HTH
Joelle

Jorge Bastos wrote:
> DecimalHi people,
>
>
> Is there any procedure to make the group function faster?
> I mean, i have a tabel with 200.000 record and about 8 fields, a command like:
> ---
> select username,count(*) from table group by username
> ---
>
> will take about 4/5 secunds
>
> a simple select count(*) from table
>
> will take 0.00045 secunds ( more or less )
>
> here's the table description:
>
> +---------------+---------------+------+-----+---------------------+-------+
> | Field         | Type          | Null | Key | Default             | Extra |
> +---------------+---------------+------+-----+---------------------+-------+
> | username      | varchar(255)  |      | MUL |                     |       |
> | filename      | text          |      |     |                     |       |
> | file_bytes    | bigint(15)    |      |     | 0                   |       |
> | remote_host   | varchar(255)  |      |     |                     |       |
> | remote_ip     | varchar(255)  |      |     |                     |       |
> | command       | varchar(255)  |      |     |                     |       |
> | transfer_time | decimal(12,5) |      |     | 0.00000             |       |
> | date_time     | datetime      |      |     | 0000-00-00 00:00:00 |       |
> | info          | varchar(2)    |      |     |                     |       |
> +---------------+---------------+------+-----+---------------------+-------+
>
>
> Jorge
>   
Thread
group slowJorge Bastos14 Apr
  • Re: group slowJoelle Tegwen14 Apr
RE: group slowjbonnett18 Apr