any way to speed up select distinct

[Jay]

I have a table with a varchar(255) column, it has 111,000 rows. When I do a
select distinct on that column it takes 16 seconds and returns about 25
distinct values, I'd like it to take much less time. I tried creating an
index but the explain on the query shows that it isn't using an index. Is
there any way to speed this up or should I just maintain another table with
the distinct values myself?

[Aggro]

Jay wrote:

I have a table with a varchar(255) column, it has 111,000 rows. When I do a
select distinct on that column it takes 16 seconds and returns about 25
distinct values, I'd like it to take much less time. I tried creating an
index but the explain on the query shows that it isn't using an index. Is
there any way to speed this up or should I just maintain another table with
the distinct values myself?

If the index is not used, you need to make the index work to speed it up.

You might want to look this manual page (don't be afraid to continue to
the group by-section as suggested and continue from there):

http://dev.mysql.com/doc/mysql/en/DI...imization.html

[Bill Turczyn]

Addind the correct indexes will definitely speed the query up, might
also want to look at turning the query cache on, after you've added
the necessary indexs to the table. This is a good article that
discusses the query cache. Also turn on logging for slow queries, this
will allow for future optimiztion.

http://www.linux-mag.com/2004-09/mysql_01.html
http://dev.mysql.com/doc/mysql/en/Slow_query_log.html

[Bill Turczyn]

Addind the correct indexes will definitely speed the query up, might
also want to look at turning the query cache on, after you've added
the necessary indexs to the table. This is a good article that
discusses the query cache. Also turn on logging for slow queries, this
will allow for future optimiztion.

http://www.linux-mag.com/2004-09/mysql_01.html
http://dev.mysql.com/doc/mysql/en/Slow_query_log.html