I am reluctant to add to SQLite the ability to explicitly specify the
index for a query. I agree with Alex Scotti that the whole idea seems
very un-RDBMS like.
On the other hand, just because a feature is there does not mean
people have to use it. The documentation can make it clear that the
feature should be used rarely and only be experts. We can make
arrangements to omit the feature at compile-time (or perhaps to
require a special compile-time option to enable it.) And, there
really do seem to be a few rare cases where explicitly naming the
index is helpfull.
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?
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
