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
>
