On Tue, Sep 23, 2008 at 01:35:44PM -0500, Jay A. Kreibich wrote: > If there was a point I was trying to make, it was that something > being "un-RDBMS like" in itself doesn't make it a bad thing. After > all, the very concept of indexes themselves is (from a Relational > Model theory viewpoint) "un-RDBMS like," but most of us are very very > comfortable with them, and accept the need to occasionally manually > create them to improve practical real-world performance.
+1 > 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. SQLite3 doesn't do that. If indexes are exposed to the user, then the user should have more control over the query optimizer's use of indexes than DROP INDEX and CREATE INDEX. How much control, I don't know. But being SQ_Lite_, and given that the 'lite' part is often advertised as relating to footprint, I'd think that whatever syntax requires the least amount of code, while not painting SQLite3 too much into a corner, would be the way to go. I've no idea then if simple directives like Dr. Hipp proposes, or something more complex, like MySQL's, would be best. > > an index surely doesn't break the relational model in any way. > > Of course not. No more than the sky being blue. "Indexes are outside > of the Relational Model." As long as they stay outside, they > shouldn't matter to the Model. > > But that doesn't mean they're outside the developer experience. Like the garbage collector in many a high level language. You could do without a GC... for a while, but mostly you need it, even if it's transparent. And when the GC is critical, then you want to be able to tune it. > > on the other hand we have here a non standard sql extension which ties > > users to sqlite, and blatantly does fly in the face of the relational > > model. > > It doesn't "blatantly" anything. Indexes are outside of the > Relational Model and have nothing to do with it. They're orthogonal. > From that, anything having to do with creating, using, or > manipulating indexes is outside the model. 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. I suspect that "impact on syntax" is taken to be evil by some. I don't disagree, but you can't have as much precise control over index use if you put the directives outside the statement, not without breaking more abstractions. Also, API-only directives would be harder to use from the shell... > Within any actual real-world RDBMS product, however, once you accept > the need for indexes (on any level) then it shouldn't be hard to > accept the desire to control how those indexes are used. > > I find it much more odd that people expect the system to magically > understand when to use or not use an index perfectly in every odd > case, but don't think twice about the fact that we need to manually > create (or not) those indexes to start with. The status quo is > this strange kind of second-guessing tuning game between the DBA > and the optimizer. +1 Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
