Yeah, I have a similar box like yours. I copied the first column to a new
table with an index. I ran select distinct and the query took 6 seconds to
execute. This must have to do with the record length, because when I indexed
the origional table's first column the query was 1 minute 30 seconds to
no
Cc: '[EMAIL PROTECTED]'
Subject: Re: Select distinct speed on an indexed column
Since your primary key is used, maybe you should consider adding an
index on PostedZpdi field only.
You may also check 'key_buffer_size' value (show variables like 'key%').
Since your
>
> From: Haydies [mailto:[EMAIL PROTECTED]
> Sent: Monday, September 15, 2003 11:19 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Select distinct speed on an indexed column
>
>
> Its a compound key, they are always slow. I would imagin you will need to
> seriously rede
Since your primary key is used, maybe you should consider adding an
index on PostedZpdi field only.
You may also check 'key_buffer_size' value (show variables like 'key%').
Since your primary key is more than 200 MB big, allocating a "big" key
buffer (> 256MB) may help.
Joseph Bueno
Nathan Cassano
mysql> explain select distinct AccountLevelId from PostedZpdi;
++---+---+-+-+--+-+-
+
| table | type | possible_keys | key | key_len | ref | rows|
Extra |
++---+---+-+--
Its a compound key, they are always slow. I would imagin you will need to
seriously redesign your database to speed that up. I'm not 100% sure how the
index is stored, but it would be some what pointless if it was individual
field values. Its like haveing "field1field2feild3field4field5" so that a
Nathan Cassano wrote:
Hey everyone,
I have a question about the speed of selecting distinct values on an
indexed column. I have a table with a five column primary key and 3,215,540
records. I want to select all of the distinct values of the first column in
the primary key. This column only
