mysql - Order by index usage -

mysql - Order by index usage -

i researching peculiar order issue have encountered sugarcrm think next test case describes:

given next 2 tables:

create table test1 ( id int(11) not null auto_increment, name char(20), primary key (id) ); create table test2 ( id int(11) not null auto_increment, name char(20), name2 varchar(10), primary key (id) );

insert random info table test1:

delimiter $$ create procedure randomizer() begin declare int default 0 ; declare random char(20) ; declare random2 char(10) ; myloop: loop set random=conv(floor(rand() * 99999999999999), 20, 36) ; insert test1 (id, name) values (i+1,random) ; set i=i+1; if i=1000 leave myloop;

end if; end loop myloop; end $$ delimiter ;

insert random info table test2:

delimiter $$ create procedure randomizer() begin declare int default 0 ; declare random char(20) ; declare random2 char(10) ; myloop: loop set random=conv(floor(rand() * 99999999999999), 20, 36) ; set random2=conv(floor(rand() * 999999), 10, 36) ; insert test2 (id, name, name2) values (i+1,random, random2) ; set i=i+1; if i=1000 leave myloop;

end if; end loop myloop; end $$ delimiter ;

add secondary indexes:

alter table test1 add together index(name); alter table test2 add together index(name);

execute qep using table bring together order on first table in join:

explain select test1.name, test2.name test1 left bring together test2 on test1.id=test2.id order test1.name +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | 1 | simple | test1 | index | null | name | 21 | null | 981 | using index | | 1 | simple | test2 | eq_ref | primary | primary | 4 | test.test1.id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

and 1 time again order on sec table in join:

explain select test1.name, test2.name test1 left bring together test2 on test1.id=test2.id order test2.name +----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+ | 1 | simple | test1 | index | null | name | 21 | null | 981 | using index; using temporary; using filesort | | 1 | simple | test2 | eq_ref | primary | primary | 4 | test.test1.id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+

i not understand why query 2 uses filesort while query 1 able utilize index. possible encountering next limitation described in document?

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

"you joining many tables, , columns in order not first nonconstant table used retrieve rows. (this first table in explain output not have const bring together type.)"

you've correctly identified why sec query isn't using index.

since you've doing left join test1 test2, test1 first nonconstant table used retrieve rows, columns test2 can't sorted using index.

i don't think there's way query maintain same functionally utilize index test2... if alter bring together type left join inner join, should create utilize of index.

mysql sql-order-by sugarcrm