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@stripped]
Sent: Friday, 14 April 2006 7:18 PM
To: win32@stripped
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
