On 9/22/08, Steve Friedman <[EMAIL PROTECTED]> wrote: > > >> > >> There seems to be no standard SQL way of providing hints to the query > >> optimizer for which index to use. Every SQL database engine does it > >> differently. The MySQL approach is the simplest by far. But even it > >> is more complex than is really needed. I propose syntax for SQLite as > >> follows: > >> > >> SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...; > >> SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...; > >> SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...; > >> > >> I further propose that if the specified index cannot be used, the > >> sqlite3_prepare_v2() call will fail with an error. In other words, > >> the new syntax is a requirement, not a hint. > >> > >> Comments? Objections? > >> > > > As a pedant, I have two comments: > > - INDEX BY is a verb form. I would think that INDEXED BY (a past > participle) would be more accurate syntax since no new indices are being > constructed.
How about USING INDEX SELECT * FROM tablex USING INDEX indexy WHERE...; SELECT * FROM tablex USING INDEX ROWID WHERE ...; SELECT * FROM tablex NOT USING INDEX WHERE ... ; > > - I presume that the following is not contemplated (and the > documentation should reflect this just to avoid surprises): > SELECT * FROM (some sub-query) AS t INDEX BY ... > > > Steve Friedman > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.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
