On Sep 25, 2008, at 10:37 PM, Alex Scotti wrote:
read http://www.ibm.com/developerworks/db2/library/tips/dm-0312yip/
index.html as well if you could.
i implore you all to take the high road here.
I agree with philosophy expressed at the link above: If [the RDBMS]
does not choose the
I have another scenario where this is needed , the one in the subject.
I repeated this problem this AM.
I need a 2 key index for some queries and also want to aggregate on
these 2 columns. I need this index BUT I have many large sqlite dbs I
iterate over and they won't fit in the filesystem
Perfect solution as long as there is a no index option along with
index by.
On Sep 26, 2008, at 12:54 PM, Russell Leighton [EMAIL PROTECTED]
wrote:
I have another scenario where this is needed , the one in the subject.
I repeated this problem this AM.
I need a 2 key index for some
On Fri, Sep 26, 2008 at 12:54:36PM -0400, Russell Leighton wrote:
I need a 2 key index for some queries and also want to aggregate on
these 2 columns. I need this index BUT I have many large sqlite dbs I
iterate over and they won't fit in the filesystem cache. Run time when
the index is
Sqlite would need to know if the file was cached or not to make the
right decision.
The big web site where every user has their own db is a perfect example.
Assume that after a user logs in that their db gets cached (because
they do many queries) and they do some aggregation, hence it runs
On Fri, 26 Sep 2008 12:54:36 -0400, Russell wrote:
I need a 2 key index for some queries and also want to aggregate on
these 2 columns. I need this index BUT I have many large sqlite dbs I
iterate over and they won't fit in the filesystem cache. Run time when
the index is present is 105min.
On Sep 26, 2008, at 2:29 PM, Kees Nuyt wrote:
On Fri, 26 Sep 2008 12:54:36 -0400, Russell wrote:
I need a 2 key index for some queries and also want to aggregate on
these 2 columns. I need this index BUT I have many large sqlite dbs I
iterate over and they won't fit in the filesystem cache.
On Sep 24, 2008, at 4:17 PM, Nicolas Williams wrote:
But every commercial SQL RDBMS seems to have syntax for index control.
as [EMAIL PROTECTED] was kind enough to post on sept 21 to
this very mailing list, not all sql rdbms have taken this approach.
at least one, db2, chose the high
On Wed, Sep 24, 2008 at 01:35:40AM -0400, Alex Scotti wrote:
On Sep 24, 2008, at 1:13 AM, Alex Scotti wrote:
On Sep 23, 2008, at 2:35 PM, Jay A. Kreibich wrote:
It doesn't blatantly anything. Indexes are outside of the
Relational Model and have nothing to do with it. They're
On Mon, Sep 22, 2008 at 10:44:20PM -0400, Alex Scotti scratched on the wall:
On Sep 22, 2008, at 11:18 AM, Jay A. Kreibich wrote:
On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched on
the wall:
I am reluctant to add to SQLite the ability to explicitly specify the
index for
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)
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
On Sep 24, 2008, at 1:13 AM, Alex Scotti wrote:
On Sep 23, 2008, at 2:35 PM, Jay A. Kreibich wrote:
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,
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
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
On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched on the wall:
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.
Well it is outside of the Relational Model,
On Sep 22, 2008, at 10:53 AM, Jeffrey Becker wrote:
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.
If you want the
On Mon, Sep 22, 2008 at 8:23 AM, D. Richard Hipp [EMAIL PROTECTED] wrote:
In the two high-profile use cases, the programmers already have the
statement using the correct index without an INDEX BY clause. They
just want to be alerted if some future schema change alters the index
choice,
To me this is a very rational approach. It is simple and unambiguous to
understand and use and simple to implement compared to the alternative
schemes. That fits nicely with the lite approach.
Ad the directive to the SQL and measure the result and the effect is
immediately obvious. Hard to
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
Why something like USE INDEX a,b,c?
On Mon, Sep 22, 2008 at 5:42 PM, Steve Friedman [EMAIL PROTECTED] wrote:
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
On 9/22/08, Steve Friedman [EMAIL PROTECTED] wrote:
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
Seems to me,
That maybe index by might be better served as an access by key phrase
instead. That way the access to the table can be described, join order etc. Not
just pick an index. That way you can programatically describe the access
path, index, full scan, rowid and potentially the join
On Sep 22, 2008, at 11:18 AM, Jay A. Kreibich wrote:
On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched
on the wall:
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
24 matches
Mail list logo