Hi, The database can't use the index on the order by column if it is already using another index to speed up the join. Basically, the database can only use one of the indexes, not two at a time. It looks like I need to improve the documentation about how databases work, specially how indexes are used. This is not really specific to H2, it applies to all relational databases.
Regards, Thomas P.S. Example: drop table details; drop table master; create table master(filepath int primary key, usr int, data varchar(255)); create table details(filepath int, action int, data varchar(255)); alter table details add foreign key(filepath) references master(filepath); create index idx_user on master(usr); create index idx_action on details(action); @loop 10000 insert into master values(?, ?, 'Hello World Hello World Hello World'); @loop 10000 insert into details values(?, 10000-?, 'Hello World Hello World Hello World'); SELECT * FROM details, master WHERE details.filepath = master.filepath ORDER BY details.action, master.usr LIMIT 10; explain plan for SELECT * FROM details, master WHERE details.filepath = master.filepath ORDER BY details.action, master.usr LIMIT 10; -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
