SELECT DISTINCT vs UNIQUE KEY

[Martin Feng]

I have a query that return around 15000 VARCHAR(255). I tried 'SELECT DISTINCT' to remove duplicates from the result set, and 'show processlist' shows mysql is using more than 15 minutes to 'remove duplicates'. I killed it before it ends.

Then I tried another way:

CREATE TEMPORARY TABLE TBL_TMPNAMES
(
COL_NAME VARCHAR(255) NOT NULL,
PRIMARY KEY (COL_NAME)
) ENGINE = MyISAM;

and use 'INSERT INGORE TBL_TMPNAMES SELECT ...'

The whole INSERT statement ends in 51 seconds, in which the select itself takes about 40 seconds.

Why does this happen?

My environment:

MySQL 5.0.22, installed from MySQL-server-standard-5.0.22-0.rhel4.i386.rpm and MySQL-Max-5.0.22-0.i386.rpm,
OS: CentOS 4.3
DB: CHARACTER SET utf8 COLLATE utf8_general_ci
All tables involved are MyISAM.