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 single comparison of the values tells you if its unique. You might be able to speed it up by putting a secondary index on the first field....
I'm curious as to why any one would design a database with that many feilds in the primary key? Maybe its me but that would just be wrong. I'm not to happy when I have 2 fields in the primary key.... ----- Original Message ----- From: "Nathan Cassano" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 15, 2003 5:07 PM Subject: Select distinct speed on an indexed column : : 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 has 549 distinct values. To execute this : query takes about a minute and a half on a P4 2.4G. I assume that mysql is : doing a complete table scan thus making the query slow. : My question follows. Shouldn't the distinct values of the first : column in an index be pulled from the index itself instead from the actual : data? Thanks for your insights! : : Nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
