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
