I am using mysql verison 3.23.41-nt I am working with the following two tables.
mysql> show create table table1; | Table | Create Table --------------------------------- | table1 | CREATE TABLE `table1` ( `Column1` varchar(63) NOT NULL default '', `Column2` varchar(31) default NULL PRIMARY KEY (`Column1`) ) TYPE=MyISAM | -------------------------------------------- mysql> show create table table2; | Table | Create Table ------------------------------------------ | table2 | CREATE TABLE `table2` ( `Column1` varchar(63) default NULL KEY `idx1` (`Column1`) ) TYPE=MyISAM | +--------+-------------------------------- 1) When i run the first query through explain the expected results happen mysql> explain SELECT table1.column1 FROM table1, table2 WHERE table1.column1 = table2.column2; +-------------+-------+---------------+---------+---------+-----------------------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+-------+---------------+---------+---------+-----------------------+-------+-------------+ | table1 | index | PRIMARY | PRIMARY | 63 | NULL | 57379 | Using index | | table2 | ref | idx1 | idx1 | 64 | table1.column1 | 1 | Using index | +-------------+-------+---------------+---------+---------+-----------------------+-------+-------------+ However, when i select an additional column, the following unexpected results happen mysql> explain SELECT table1.column1, table1.column2 FROM table1, table2 WHERE table1.column1 = table2.column2 +-------------+------+---------------+------+---------+-----------------------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+------+---------------+------+---------+-----------------------+-------+-------------+ | table1 | ALL | PRIMARY | NULL | NULL | NULL | 57379 | | | table2 | ref | idx1 | idx1 | 64 | table1.column1 | 1 | Using index | +-------------+------+---------------+------+---------+-----------------------+-------+-------------+ 2) Also, look at the following scanario mysql> explain SELECT table1.column1 FROM table1, table2 WHERE table1.column1 = table2.column2 order by table1.column1 asc; +-------------+-------+---------------+---------+---------+-----------------------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+-------+---------------+---------+---------+-----------------------+-------+-------------+ | table1 | index | PRIMARY | PRIMARY | 63 | NULL | 57379 | Using index | | table2 | ref | idx1 | idx1 | 64 | table1.column1 | 1 | Using index | +-------------+-------+---------------+---------+---------+-----------------------+-------+-------------+ However, when i select an additional column, the following unexpected results happen mysql> explain SELECT table1.column1, table1.column2 FROM table1, table2 WHERE table1.column1 = table2.column2 order by table1.column1 asc +-------------+------+---------------+------+---------+-----------------------+-------+----------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+------+---------------+------+---------+-----------------------+-------+----------------+ | table1 | ALL | PRIMARY | NULL | NULL | NULL | 57379 | Using filesort | | table2 | ref | idx1 | idx1 | 64 | table1.column1 | 1 | Using index | +-------------+------+---------------+------+---------+-----------------------+-------+----------------+ I am baffled as to why simple selecting an extra column results in the index no longer being used even though the where clause has not changed. If anybody could provide me an explain as to why this is occuring with a solution, it would be greatly appreciated. TIA __________________________________________________ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
