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
