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:[email protected]] 
Sent: Friday, 14 April 2006 7:18 PM
To: [email protected]
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/[email protected]