>> >> 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. - 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