Re: [sqlite] General index information
* Igor Tandetnik: > L B wrote: >> The explanation you made is only valid for sqlite or >> for SQL in general? > > For SQL in general. True, but there are some database engines that support skip-index scans, so if there are only few different values of X, say x_1, ... x_k, you've got an index on (X, Y) and want to find all y, the query is executed as lookups for (x_1, y), ..., (x_k, y). (Obviously, this is only beneficial if k is not too big.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
Thanks Igor,your examples really helped me! --- Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "L B" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] > > Just to better understand, the index on (x,y,z), > would > > it be useful for queries like > > > > select * from table1 inner join table2 > > on table1.x=table2.x and table1.y = table2.y and > > table1.z=table2.z > > Yes. > > > or > > > > "table1.y = table2.y and table1.z=table2.z" would > not > > benefit the index > > Correct. > > > (so it would be better 3 different > > indexes) > > ... or a single index on (y, z) > > > because it is useful just for "field x" > > comparison or to retrieve y and z values given x? > > ... or to retrieve z value given x and y. And also > for various > inequalities and sorts, e.g. > > select * from mytable where x between 100 and 200; > select * from mytable where x=5 and y between 100 > and 200; > select * from mytable where x=5 and y=6 and z > between 100 and 200; > > select * from mytable order by x; > select * from mytable where x=5 order by y; > select * from mytable where x=5 and y=6 order by z; > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
"L B" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Just to better understand, the index on (x,y,z), would > it be useful for queries like > > select * from table1 inner join table2 > on table1.x=table2.x and table1.y = table2.y and > table1.z=table2.z Yes. > or > > "table1.y = table2.y and table1.z=table2.z" would not > benefit the index Correct. > (so it would be better 3 different > indexes) ... or a single index on (y, z) > because it is useful just for "field x" > comparison or to retrieve y and z values given x? ... or to retrieve z value given x and y. And also for various inequalities and sorts, e.g. select * from mytable where x between 100 and 200; select * from mytable where x=5 and y between 100 and 200; select * from mytable where x=5 and y=6 and z between 100 and 200; select * from mytable order by x; select * from mytable where x=5 order by y; select * from mytable where x=5 and y=6 order by z; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
Just to better understand, the index on (x,y,z), would it be useful for queries like select * from table1 inner join table2 on table1.x=table2.x and table1.y = table2.y and table1.z=table2.z or "table1.y = table2.y and table1.z=table2.z" would not benefit the index (so it would be better 3 different indexes) because it is useful just for "field x" comparison or to retrieve y and z values given x? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
L B <[EMAIL PROTECTED]> wrote: > The explanation you made is only valid for sqlite or > for SQL in general? For SQL in general. > Moreover, the query: > > select * from mytable where X=?; > > would still benefit the index on (X, Y, Z)? Yes. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
Thank you very much for your reply, Igor. The explanation you made is only valid for sqlite or for SQL in general? Moreover, the query: select * from mytable where X=?; would still benefit the index on (X, Y, Z)? --- Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "L B" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] > > I would like to know if there is any difference > > between using 3 indexes on three different fields > or > > just an index which groups them, in terms of > physical > > size and performance. > > Yes. In both cases, there are queries that will > benefit from such > indexes, and queries that won't be able to use them. > > > I have seen that sqlite cannot use more than one > index > > at a time, so I was wondering if the second option > is > > always the best solution. > > Not always. If you have a single index on (X, Y, Z), > it can't be used > for queries like > > select * from mytable where Y=?; > select * from mytable where Z=?; > > for the same reason that the sort order in a > dictionary doesn't help you > find all words whose second letter is 'a'. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
On Dec 10, 2008, at 7:48 AM, Igor Tandetnik wrote: > >> I have seen that sqlite cannot use more than one index >> at a time, so I was wondering if the second option is >> always the best solution. > > Not always. If you have a single index on (X, Y, Z), it can't be used > for queries like > > select * from mytable where Y=?; > select * from mytable where Z=?; > > for the same reason that the sort order in a dictionary doesn't help > you > find all words whose second letter is 'a'. Excellent analogy, Igor! I should work that into the SQLite documentation somehow. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
"L B" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I would like to know if there is any difference > between using 3 indexes on three different fields or > just an index which groups them, in terms of physical > size and performance. Yes. In both cases, there are queries that will benefit from such indexes, and queries that won't be able to use them. > I have seen that sqlite cannot use more than one index > at a time, so I was wondering if the second option is > always the best solution. Not always. If you have a single index on (X, Y, Z), it can't be used for queries like select * from mytable where Y=?; select * from mytable where Z=?; for the same reason that the sort order in a dictionary doesn't help you find all words whose second letter is 'a'. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General index information
On 12/10/08, L B <[EMAIL PROTECTED]> wrote: > Hi all, > > I would like to know if there is any difference > between using 3 indexes on three different fields or > just an index which groups them, in terms of physical > size and performance. > I have seen that sqlite cannot use more than one index > at a time, so I was wondering if the second option is > always the best solution. Let's say you have a table like so CREATE TABLE foo (a, b); and, (a, b) are unique combinations. If you want to search for 'b' for a given 'a', that is SELECT b FROM foo WHERE a = ? then an index on (a, b) would be useful. sqlite would look in the index for 'a = ?', and on finding it, would also immediately find the corresponding value of 'b' without having to go back to the table, go to the corresponding row with the 'a' value just looked up in the index and retrieve the 'b' value. Richard Hipp provided a very nice explanation in an email message titled "Re: [sqlite] An explanation?" dated 4/26/07. Search for it in the email archives. By the way, listers -- is there a way to provide a URI to a specific message in the archives? if yes, how? Say, I want to write an article, and want to link to a particular email message, what is the right (dependable and long-term, preferably to the official archives and not to a mirror such as nabble) way of doing so? -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] General index information
Hi all, I would like to know if there is any difference between using 3 indexes on three different fields or just an index which groups them, in terms of physical size and performance. I have seen that sqlite cannot use more than one index at a time, so I was wondering if the second option is always the best solution. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
