Re: [sqlite] Re: CREATE INDEX column order
The index is a B-Tree, not hashed. The order of the segments of the key makes a big difference to queries, as pointed out earlier. It doesn't make any significant difference to the time it takes to create of maintain the index. JS Taka wrote: Ah, maybe I wasn't quite clear enough. What I meant was, is there any performance difference between: CREATE INDEX ON my_table ( a , b , c ) and CREATE INDEX ON my_table ( c , b , a ) I'm guessing not, presumably because the index is using some kind of hashing but I thought I'd ask, just to be sure :-) Taka wrote: Does it make a difference what order the columns are specified when creating an index? Yes. An index on colums (a, b, c, d) can also speed up search on column a, on a pair of columns (a, b) and on a triple (a, b, c). If you have a query with a where clause looking like "where a=1 and b=2" the matching records will be found using the index. But if you have "where a=1 and c=2" then all records with a=1 will be found using the index, then a sequential scan through those records will be needed to find records matching c=2 Igor Tandetnik
Re: [sqlite] Re: CREATE INDEX column order
Ah, maybe I wasn't quite clear enough. What I meant was, is there any performance difference between: CREATE INDEX ON my_table ( a , b , c ) and CREATE INDEX ON my_table ( c , b , a ) I'm guessing not, presumably because the index is using some kind of hashing but I thought I'd ask, just to be sure :-) > Taka wrote: >> Does it make a difference what order the columns are specified when >> creating an index? > Yes. An index on colums (a, b, c, d) can also speed up search on column > a, on a pair of columns (a, b) and on a triple (a, b, c). If you have a > query with a where clause looking like "where a=1 and b=2" the matching > records will be found using the index. But if you have "where a=1 and > c=2" then all records with a=1 will be found using the index, then a > sequential scan through those records will be needed to find records > matching c=2 > Igor Tandetnik
[sqlite] Re: CREATE INDEX column order
Taka wrote: Does it make a difference what order the columns are specified when creating an index? Yes. An index on colums (a, b, c, d) can also speed up search on column a, on a pair of columns (a, b) and on a triple (a, b, c). If you have a query with a where clause looking like "where a=1 and b=2" the matching records will be found using the index. But if you have "where a=1 and c=2" then all records with a=1 will be found using the index, then a sequential scan through those records will be needed to find records matching c=2 Igor Tandetnik
