3 messages in com.mysql.lists.win32RE: group slow| From | Sent On | Attachments |
|---|---|---|
| Jorge Bastos | 14 Apr 2006 02:47 | |
| Joelle Tegwen | 14 Apr 2006 07:20 | |
| jbon...@sola.com.au | 17 Apr 2006 16:20 |
| Subject: | RE: group slow |
|---|---|
| From: | jbon...@sola.com.au (jbon...@sola.com.au) |
| Date: | 04/17/2006 04:20:19 PM |
| List: | com.mysql.lists.win32 |
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:mysq...@decimal.pt] Sent: Friday, 14 April 2006 7:18 PM To: win...@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