List:MySQL on Win32« Previous MessageNext Message »
From:<jbonnett Date:April 17 2006 11:20pm
Subject:RE: group slow
View as plain text  
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
Thread
group slowJorge Bastos14 Apr
  • Re: group slowJoelle Tegwen14 Apr
RE: group slowjbonnett18 Apr