Hi, I'm having a hard time figuring why mySQL does not use the index when sorting a table without any where clause when I include more than 1 field in the select part. To resume the case, I created a temp table with 3 single fields, added an index (see bottom for more details), ran 2 queries with explain: This one is fine, uses the index: mysql> explain select isbn from tmp order by isbn\G *************************** 1. row *************************** table: tmp type: index possible_keys: NULL key: isbn key_len: 13 ref: NULL rows: 1000 Extra: Using index 1 row in set (0.00 sec) Adding just an extra field to the select and no longuer is the index used: mysql> explain select isbn,author from tmp order by isbn\G *************************** 1. row *************************** table: tmp type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using filesort 1 row in set (0.00 sec) When used with the real table which has 160k rows and is called within a complex join that works fine (0.04 sec) and uses index until I add an order by clause, then takes 12secs. Help or hints on fixing the above will probably fix my real problem. Thanks for any help. Further details below, including mySQL version (3.23.30) and OS (RedHat 6.0). Francois mysql> CREATE TEMPORARY TABLE tmp select isbn,author,title from ws_inventory where available = 1 limit 1000; Query OK, 1000 rows affected (0.04 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> alter table tmp add index(isbn); Query OK, 1000 rows affected (0.09 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> explain tmp\G *************************** 1. row *************************** Field: isbn Type: char(13) Null: Key: MUL Default: Extra: Privileges: select,insert,update,references *************************** 2. row *************************** Field: author Type: char(60) Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references *************************** 3. row *************************** Field: title Type: char(255) Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references 3 rows in set (0.00 sec) mysql> explain tmp; +--------+-----------+------+-----+---------+-------+----------------------- ----------+ | Field | Type | Null | Key | Default | Extra | Privileges | +--------+-----------+------+-----+---------+-------+----------------------- ----------+ | isbn | char(13) | | MUL | | | select,insert,update,references | | author | char(60) | YES | | NULL | | select,insert,update,references | | title | char(255) | YES | | NULL | | select,insert,update,references | +--------+-----------+------+-----+---------+-------+----------------------- ----------+ 3 rows in set (0.00 sec) mysql Ver 11.10 Distrib 3.23.30-gamma, for pc-linux-gnu (i686) Connection id: 157 Current database: schoen Current user: schoen_root@localhost Current pager: stdout Using outfile: '' Server version: 3.23.30-gamma Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 1 day 21 hours 24 min 47 sec Threads: 1 Questions: 655 Slow queries: 38 Opens: 89 Flush tables: 2 Open tables: 23 Queries per second avg: 0.004 --------------------------------------------------------------------- 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