Hi,
I think you are mis-understanding the information provided. What the "using
index" note means is that the isbn column is part of the index, and since
that is all that is selected, the index file is used to provide the data,
the data file is not touched. Since there is (usually) less data in the
index file, and it is more organised, the query takes less time.
Adding the author to the query means that the query now has to go to the
data file to get the information, and since there is no restriction on isbn,
the entire table is scanned for records to provide the result, which is then
sorted. If you had an index of (isbn, author), then the index file would
still be used.
The clue is in the 'possible keys' value - NULL means that no keys are
available that would help in providing the query.
Can someone else tell me and Francois, when the index is used like this, is
the result pre-sorted, so the order by is trivial, or is a sort still
performed.
Hope this helps
Regards
Quentin
-----Original Message-----
From: François Bélanger [mailto:[EMAIL PROTECTED]]
Sent: Friday, 16 February 2001 08:01
To: [EMAIL PROTECTED]
Subject: Index not used for order by when more than 1 field selected, no
where clause
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
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
---------------------------------------------------------------------
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
