On Feb 11, 1:20 pm, Thomas Mueller <[email protected]> wrote: > 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.
I beg to disagree. In case of SELECT * FROM details JOIN master ON master.filepath = details.filepath ORDER BY details.action LIMIT 10; the details may be read using idx_action and the corresponding rows from master may be found using its PK. IMHO, this should work, since it reduces the work considerably. I'm quite sure it works with MySql. With ORDER BY details.action, master.usr the situation is much more complicated, but a similar optimization applies. Details can still be read using idx_action, when you get 10 rows, than you continue reading until a different action occurs. This way you get 10+ rows and know you won't need more. With good selectivity of action (like in your example) you can save a lot of work. -- 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.
