Improving count(distinct) - group by - query
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 |
+----------+
Subject
Views
Written By
Posted
Improving count(distinct) - group by - query
4724
February 10, 2005 09:50AM
2225
February 18, 2005 02:54AM
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.