On 3 Mar 2015, at 11:10am, Eric Grange <zarglu at gmail.com> wrote:
> With indexes on A & B, the performance of the above is not very good, as
> indexing A1 is not enough, and indexing B1 is not enough either, so no
> query plan is satisfying.
The B1 index isn't going to be used. Here is your query:
select ...some fields of A & B...
from A join B on A.A2 = B.B2
where A.A1 = ?1
order by B.B1
limit 100
An index on A.A1 is good, since it satisfies the "WHERE" clause. So do that as
you did.
Once SQLite has found relevant rows of A it's processing the "JOIN". This
means going through table B.B2 looking for rows which match existing values of
A.A2. You need an index on B.B2. But having done that the "ORDER BY" clause
means it needs to order the resulting rows by B.B1. So one index which allowed
both might provide the best advantage. Try
CREATE INDEX B_2_1 ON B (B2, B1)
then do an ANALYZE, and see if that helps matters. Another approach is to look
at your "ORDER BY" clause and see that what you're doing is based on ordering
by B.B1, sp another way to see your query is
select ...some fields of A & B...
from B join A on A.A2 = B.B2
where A.A1 = ?1
order by B.B1
limit 100
This would be best helped with an index on B.B1 and another on A.A1 and A.A2.
I suggest you do
CREATE INDEX B_1_2 ON B (B1, B2)
CREATE INDEX B_2_1 ON B (B2, B1)
CREATE INDEX A_1_2 ON A (A1, A2)
CREATE INDEX A_2_1 ON A (A2, A1)
then do an ANALYZE, then execute the query as rephrased above. If this turns
out to be faster you can use EXPLAIN QUERY PLAN to find out which indexes
SQLite is doing and delete the unused ones.
Simon.
