On Mar 21, 8:28 pm, Thomas Mueller <[email protected]> wrote: > OK I change my sentence to: "The database can't use the index on the > order by column if it is already using another index *on the same > logical table* to speed up the join."
This wording is surely right, and the behavior described so far, too. But the plan is sometimes wrong: The following example leads to "index sorted" and 0 ms. DROP ALL OBJECTS; CREATE TABLE master (filepath int PRIMARY KEY, usr int); CREATE TABLE details (filepath int NOT NULL, action int); -- CREATE PRIMARY KEY ON details (filepath); CREATE INDEX idx_user ON master(usr); CREATE INDEX idx_action ON details(action); @loop 1000000 INSERT INTO master VALUES (?, ?); @loop 1000000 INSERT INTO details VALUES (?, 10000-?); EXPLAIN ANALYZE SELECT * FROM details JOIN master ON details.filepath = master.filepath ORDER BY details.action LIMIT 10; Removing the "--" leads to using the other PK and sorting which takes 2187 ms. In such a case both possible indexes should be examined and the one allowing index sort chosen. -- 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.
