On Tue, Sep 23, 2008 at 02:26:08PM -0500, Nicolas Williams scratched on the wall: > On Tue, Sep 23, 2008 at 01:35:44PM -0500, Jay A. Kreibich wrote:
> > IMHO, the jump from "you must manually create indexes" to "you may > > control the *use* of an index" is a MUCH smaller jump than the very > > concept of "CREATE INDEX." After all, if the RDBMS is supposed to be > > smart enough to figure out when using or not using an index is a good > > idea, shouldn't it also be smart enough to figure out if creating an > > index is a good idea? Or, conversely, if nobody expects a system to > > be smart enough to know when to create them, why do we expect it to > > be smart enough to know when to use them? > > Given a finite number of [parametrized] queries that are expected to > perform well against a given schema and data, then yes, the RDBMS could > automatically create the indexes needed to optimize those queries. > That'd sure be user-friendly. Provide set of [parametrized] queries. > Analyze data. Automatically create indexes. It'd be nice too to give > the user information about the impact of indexes on writes. I hope this was clear, but I wasn't proposing anything like this for SQLite itself. I was speaking of RDBMS systems in general at a much higher level. It is, however, the kind of feature I'd like to see in some of the management tools. As you said, point it at a database, feed it a bunch of queries, and let it make suggestions. > If the API allowed one to first import a statement (from text), then > apply directives relating to index use and so on, _then_ compile the > statement, then these directives would be "outside the relational model" > at least in so far as the directives have no impact on the SQL syntax. SQL (the standard) != the Relational Model. Not even close. > I suspect that "impact on syntax" is taken to be evil by some. Personally, I think altering the SQL is the least of my concerns. When you get this nitty-gritty you're so deep into whatever specific RDBMS product you're using that custom syntax shouldn't be a worry. You're already fully committed. If anything, it will remind you that you need to re-visit the issue if you ever move the SQL to another platform. > Also, API-only directives would be harder to use from the shell... Yes, I would put forth that a "hint API" is actually against the general approach. There is a reason why the C interface doesn't include public functions to scan a table or things like that. The interface is SQL, and that's where DB engine interactions should go. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
