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
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 105m
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 fas
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
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 so
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
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
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 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 wi
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 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 viewpo
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
>
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
>> ve
s.
Just my .02.
--- On Mon, 9/22/08, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
From: Jay A. Kreibich <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Specifing which index to use. Was: Performance/bug in
multikey 'group by' in 3.6.2
To: "General Discussion of SQLite Datab
Since someone mentioned only the support in passing, not the details, I
thought I'd throw out this:
Microsoft SQL Server using the following syntax for hints:
SELECT * FROM FOO WITH (hints)
'hints' can included locking hints, index-usage hints, and whatnot.
I will quote some of the proposed ex
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
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
> c
>>
>> 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
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
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
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
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,
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 h
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 t
25 matches
Mail list logo