group slow

------=_NextPart_000_0045_01C65FB0.E1BDC130
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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
------=_NextPart_000_0045_01C65FB0.E1BDC130--

Re: group slow

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
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: group slow

The reason your second query is so fast is that it amounts to just
counting the rows in the table, but MySQL always remembers how many rows
there are, so it can give you that answer very quickly.

Your other query asks MySQL to do some real work to give the count for
each user name and so it takes longer. Having an index on username
should speed this process.

John B.

-----Original Message-----
From: Jorge Bastos [mailto:mysql.jorge@decimal.pt]=20
Sent: Friday, 14 April 2006 7:18 PM
To: win32@lists.mysql.com
Subject: group slow

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

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org