Richard,
Just a suggestion.
Would it make sense to ask one of your document maintainers to add
something similar to your explanation and add it to the "SELECT"
documentation page as a note to using the WHERE clause and the "INDEX"
documentation page
On Jul 1, 2008, at 3:53 PM, Alexey Pechnikov wrote:
> В сообщении от Tuesday 01 July 2008 23:47:50
> [EMAIL PROTECTED] написал(а):
>> On Tue, 1 Jul 2008, Alexey Pechnikov wrote:
>>> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)"
>>> and "CREATE INDEX ev_idx ON
В сообщении от Wednesday 02 July 2008 08:25:10 Dan написал(а):
> > I'm using SQLite 3.5.9 and there are no differents in my tests
> > between DESC
> > and default indeces. I try create index with keywork DESC for
> > optimize DESC
> > sorting but it don't work for me. My tests you can see
Yes, this has been my experience as well. I've tried 3.5.6 and 3.5.9.
Jeff
Alexey Pechnikov wrote:
> В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а):
>
>> On Tue, 1 Jul 2008, Alexey Pechnikov wrote:
>>
>>> Is any difference between "CREATE INDEX ev_idx ON
В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а):
> On Tue, 1 Jul 2008, Alexey Pechnikov wrote:
> > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)"
> > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc"
> > keyword for index?
>
> The
On Tue, 1 Jul 2008, Alexey Pechnikov wrote:
> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)"
> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword
> for index?
The DESC keyword creates the index in descending collation order, rather
than ascending
Is any difference between "CREATE INDEX ev_idx ON events(type,eid)"
and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword
for index?
___
sqlite-users mailing list
sqlite-users@sqlite.org
I see. It turns out that the selectivity of "type" is highly
variable - some types are very common and some are quite rare. What
made me curious is that when I have an index on type and I look for the
first few entries in ascending order, the query is very fast - it seems
that it does
On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote:
> I'm including a copy of Alexey's relevant message below. Unless I
> misunderstand, he has a test case that demonstrates that for the
> table:
>
> CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)
>
> the query:
>
> SELECT events.* FROM
I'm including a copy of Alexey's relevant message below. Unless I
misunderstand, he has a test case that demonstrates that for the table:
CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)
the query:
SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
eid DESC LIMIT
On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote:
>
> Is it a problem in sqlite that it will only optimize: "WHERE
> primary_key<=X ORDER BY primary_key DESC" if it's not using an index?
> Is it supposed to?
It would be a problem if it where the case. But in every test I have
tried,
Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, July 01, 2008 9:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Index and ORDER BY
I agree. If I drop indices that use "type", I get my performance back
for this query:
sqlite> SELECT events.* FROM events WHERE eid<
В сообщении от Tuesday 01 July 2008 19:26:47 John Stanton написал(а):
> I haven't looked closely at this problem but a cursory glance suggests
> that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause.
But primary key index work fine. Why?
> Try doing the selection ASC and then
I haven't looked closely at this problem but a cursory glance suggests
that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause.
Try doing the selection ASC and then sorting the output DESC as a
seperate action.
Alexey Pechnikov wrote:
> Really, there is problem with
Really, there is problem with multi-column indexes. You must use only primary
key index for ">=" where clause and "ASC" sorting and "<=" where clause and
DESC sorting.
1. I try with primary key:
#!/usr/bin/tclsh
package require sqlite3
sqlite3 db index_order.db
db eval {DROP TABLE IF EXISTS
When I try a similar query (i.e, no type comparison), I get the same
results as you:
sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 ORDER BY eid DESC
LIMIT 1;
3261976|21
CPU Time: user 0.00 sys 0.027996
sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE
eid<=3261976
I try with this script on my laptop with 1 Gb RAM
#!/usr/bin/tclsh
package require sqlite3
sqlite3 db index_order.db
db eval {DROP TABLE IF EXISTS events}
db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY)}
db eval {CREATE INDEX ev_desc_idx ON events(eid desc)}
db transaction {
for
H INDEX ev4_idx ORDER BY
With a different index:
sqlite> CREATE INDEX ev5_idx ON events(type,eid desc);
sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE
eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events WITH INDEX ev5_idx ORDER BY
sqlite> SELECT events
Show results of this queries:
select max(eid) from events;
select count(eid) from events;
select count(eid) from events where type=22;
select count(eid) from events where eid<=3261976;
select count(eid) from events where eid<=3261976 and type=22;
___
Can you send link to you database file? You results are strange.
В сообщении от Saturday 28 June 2008 21:44:15 [EMAIL PROTECTED] написал(а):
> Very strange. I modified my query to not use verbose or tid, so only
> the indexed columns are relevant.
>
> With:
>
> CREATE INDEX ev4_idx ON
Very strange. I modified my query to not use verbose or tid, so only
the indexed columns are relevant.
With:
CREATE INDEX ev4_idx ON events(type);
The query runs in about 9 seconds.
With:
CREATE INDEX ev4_idx ON events(type,eid desc)
It runs in 11 seconds.
I'm not using the most accurate
I tried taking the tid and verbose tests out of the WHERE clause, and
it made very little difference in the performance. I was thinking
that if I can at least speed it up with just eid and type, I could try
to extend it to the other columns.
Thanks,
Jeff
On Jun 28, 2008, at
Sounds promising. I'll give it a try. Thanks!
Jeff
On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote:
> В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson
> написал(а):
>> When I do the following query:
>>
>> SELECT events.* FROM events WHERE ( events.type=22) AND
>> (
16 million
On Jun 28, 2008, at 4:25 AM, Alexey Pechnikov wrote:
> В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson
> написал(а):
>> I have a large table and a two column index:
>
> How much rows are you have?
> ___
> sqlite-users mailing
(sorry about my poor english)
I think that the problem doesn't correct by creating a DESC index. The
problema was at de 'where clausula':
(events.eid<=3261976)
For some reason SQLITE doesn't optimize the query to use the index to
locate the last index item that have type=22 and eid<=3261976.
В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а):
> When I do the following query:
>
> SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9)
> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid
> DESC LIMIT 1;
>
> it's very slow. If I
В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а):
> I have a large table and a two column index:
How much rows are you have?
___
sqlite-users mailing list
sqlite-users@sqlite.org
Thanks for your help. I created the index:
CREATE INDEX ev4_idx ON event(type);
According to "EXPLAIN QUERY PLAN", it's being used. When I run the query:
SELECT events.* FROM events WHERE ( events.type=22) AND
(events.eid<=3261976) AND (tid=9) AND (verbose<=1) ORDER BY events.eid
DESC LIMIT
On Jun 27, 2008, at 6:28 PM, Jeff Gibson wrote:
> I have a large table and a two column index:
>
> CREATE TABLE events (eid INTEGER PRIMARY KEY,
> time INTEGER,
> aid INTEGER,
> subtype INTEGER,
> type INTEGER,
>
I have a large table and a two column index:
CREATE TABLE events (eid INTEGER PRIMARY KEY,
time INTEGER,
aid INTEGER,
subtype INTEGER,
type INTEGER,
tid INTEGER,
verbose
30 matches
Mail list logo
