First, I have to agree that this is very 'un-rdbmsish'. I understand that sometimes the programmer really does know better than the DB engine which indexes it should use. However, the RDBMS is fundamentally an abstraction layer. I think the policy other dbms systems have of making these things hints rather than requirements is a good one because it still allows the query optimizer to make the best choice when the hints its given become incorrect. I'd prefer to see some sort of programmatic method of doing this. The method I'd find ideal would be to have some sort of sqlite_suggest_* api which would allow a user to apply hints to an already prepared statement. I forget if sqlite_stmt keeps a copy of the sql so I may well be suggesting the impossible here. The api would reinforce the non-standard nature of the action while keeping the sql dialect free of non-standard sql.
On Mon, Sep 22, 2008 at 10:07 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
