Re: Index not used for "select distinct"?

[gerald_clark]

Matthias Urlichs wrote:
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
 `IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
 `Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL 
default 'WARN',
 `epoch` int(10) unsigned NOT NULL default '0',
 KEY `Trap` (`IP`,`Type`,`epoch`),
 KEY `IP` (`IP`)
Your second key is redundant.
Your Key file is larger than your data file.
It would have to scan the whole keyfile anyway, so it is faster to scan 
the table.

)
... containing ten million records; the "IP" column holds only a handful
of distinct values. Given this, I would expect a "select distinct ip"
to return immediately.
However,
 

explain select distinct ip from test;
   

+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows     
| Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | IP   |      15 | NULL | 10991123 
| Using index |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
takes a *long* time and obviously scans the whole table.
Ideas, anybody? 

MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]