MySQL Forums
Forum List  »  Performance

Improving count(distinct) - group by - query
Posted by: Hector Villafuerte
Date: February 10, 2005 09:50AM

Hi!
I need to optimize this type of query. It is a count(distinct) - group by - query which is taking to long.
My machine is a WinXP, 1.7GHz, 512MB, MySQL 4.0.16-max-nt.
I wonder which server parameter I need to tweak in order to improve its performance in this queries.
Thanks!

mysql> create table tmp.tmp2
> select tel, count(distinct telefb) dest
> from trafficpcs.20050208_cdma
> group by 1 order by null;
Query OK, 1020107 rows affected (18 min 21.66 sec)
Records: 1020107 Duplicates: 0 Warnings: 0

mysql> explain trafficpcs.20050208_cdma;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| tel | char(24) | YES | MUL | NULL | |
| telefb | char(32) | YES | MUL | NULL | |
| FORGCLLI | char(4) | YES | | NULL | |
| handoffs | int(3) | YES | | NULL | |
| FTRMCLLI | char(4) | YES | | NULL | |
| CALLDUR | char(6) | YES | | NULL | |
| MINUTOS | int(9) | YES | | NULL | |
| fecha | char(8) | YES | | NULL | |
| HORA | char(6) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
9 rows in set (0.56 sec)

mysql> show index from trafficpcs.20050208_cdma;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 20050208_cdma | 1 | tel | 1 | tel | A | 1027164 | NULL | NULL | YES | BTREE | |
| 20050208_cdma | 1 | telefb | 1 | telefb | A | 1283955 | NULL | NULL | YES | BTREE | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> select count(*) from trafficpcs.20050208_cdma;
+----------+
| count(*) |
+----------+
| 5135823 |
+----------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Improving count(distinct) - group by - query
4724
February 10, 2005 09:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.