On 2014/05/08 14:13, Woody Wu wrote:
Do you mean that my index for this case is already the best choice?
And, as a general question, for a query in the form of
select max(a) from table where a < InF and b=B and c=C.
what's the best index in the case of (1) B is an existed one, or (2) B is not
exists ?
Firstly, Simon's explanation of the best Index to use is to the point and accurate, so I won't add to that here, but on the last
point I should say that there is no difference between case 1 and case 2.... you seem to be missing the entire point of why your
query performed at different speeds, and I thought Igor explained it very well... but since I am not busy now, let me try to
elaborate more on why your query worked like it worked.
Imagine you have a table like this:
A, B, C
1, 1, 1
2, 1, 2
3, 2, 1
4, 1, 3
5, 1, 1
6, 2, 1
7, 1, 2
And now you asked the qyery like this:
SELECT max(a) from table where a<INF AND b=2 and c=1;
Now the query planner knows that you are looking for a maximum value, it also knows that A is sorted up, so max(a) will be the last
value in the list for which B and C matches, right?
So the best plan here is to go down from the end of the list, starting at the last entry (a=7) and see if b=2 and c=1... which it is
not, so it goes to the next lower entry, namely a=6.. and YES, it finds that here b=2 and c=1 just like the query asked.. so it puts
the results in, and there is no need to continue checking because there can be no higher max(a) value ever again down the list...
Now change this query to ask:
SELECT max(a) from table where a<INF AND b=8 and c=1;
// i.e the value 8 for "b" does not exist in the list
Now the query planner knows that you are looking for a maximum value again, it also knows that A is sorted from least to most again,
so max(a) will again be the last value in the list for which B and C matches.
So the best plan here is to go down from the end of the list, starting at the last entry (a=7) and see if b=8 and c=1... which it is
not, so it goes to the next lower entry, namely a=6.. and still no match, so it goes to next lower entry (a=5) and still no match,
so it goes to next lower entry (a=4) etc etc etc until ALL the list is scanned through taking a LOT of extra time. The problem is
not your index, the problem is that in the one case there IS a value that qualifies soon in the search cycle, and in the other case
there never is a value that qualifies, so it has to keep looking until ALL the list has been searched - which simply takes a lot
longer. The problem is not your index... the problem is asking to find something that does not exist in a very long list.
Do you understand now why there is no "better" index that will fix your problem?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
