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`)
)
... 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.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
