I try to understand index usage for 'order by' but that's seemn hard to me. Here is what I have tried :
1_ About documentation
I have read carefully related documentation (7.2.9 How MySQL Optimizes ORDER BY) but
I don't understand it.
- What is the difference betwween 'key1' and 'key_part1' ?
- Why is it written ? :
The following query will use the index to resolve the ORDER BY
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
The following query cannot use indexes to resolve the ORDER BY SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
2_ I also tried a small example (table Test), with 2 fields and 2 (multiple) indexes
create table Test(Name varchar(64), Adress varchar(128), \ index K0(Name(16), Adress(16)), \ index K1(Adress(16), Name(16)) ) type = INNOBASE ;
What are the indexes needed for 'orber by' in the request select * from Test where Name='n1' order by Adress ;
If I ask explain, I got 'Using filesort' in Extra column information
mysql> explain select * from Test where Name='n1' order by Adress ;
+-------+------+---------------+------+---------+-------+------ +-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+-------+------ +-----------------------------+
| Test | ref | K0 | K0 | 17 | const | 1 | Using where; Using filesort |
+-------+------+---------------+------+---------+-------+------ +-----------------------------+
Bernard:
The problem is that you are indexing only a prefix of the address in the (name,address). Try key(name(16),address). In general, order by on a key or key part cannot use prefix-indexed fields.
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
