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
show table status; -------+-------------+------------+----------------+------------------------ ---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +------------------------+--------+------------+----------+----------------+ -------------+-----------------+--------------+-----------+----------------+ ------- | PostedZpdi | InnoDB | Dynamic | 3215540 | 678 | 2180988928 | NULL | 530350080 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 142368768 kB |
Hi,
What do you get when you do an EXPLAIN of your query ?
-- Joseph Bueno
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
