Re: [sqlite] optimizer question

[Dan Kennedy]

On Thu, 2007-07-19 at 22:56 +0100, Colin Manning wrote:
> Hi
> 
> If I have a table with a couple of indexed varchar fields e.g:
> 
> CREATE TABLE t (id INT, a VARCHAR(20), b VARCHAR(20)...);
> CREATE INDEX ia ON t(a);
> CREATE INDEX ib ON t(b);
> 
> then will the sqlite query optimizer use these indices in these SELECT's:
> 
> 1. SELECT * FROM t WHERE a LIKE 'M%';
> 2. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY a;
> 3. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b;
> 4. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b,a;

I think all of those queries will use index "ia".

For queries 3 and 4, a subset of table t will be scanned,
and a temporary b-tree structure used to do the ORDER BY.

Dan.



> ...such that none of them will result in a table scan?
> 
> Thx
> 
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] optimizer question

[drh]

Colin Manning <[EMAIL PROTECTED]> wrote:
> Hi
> 
> If I have a table with a couple of indexed varchar fields e.g:
> 
> CREATE TABLE t (id INT, a VARCHAR(20), b VARCHAR(20)...);
> CREATE INDEX ia ON t(a);
> CREATE INDEX ib ON t(b);
> 
> then will the sqlite query optimizer use these indices in these SELECT's:

The LIKE cannot be optimized unless you either

A)  Set PRAGMA case_sensitive_like=ON;
B)  Declare column a to have COLLATE NOCASE.
C)  Use "a GLOB 'M*'" instead

> 
> 1. SELECT * FROM t WHERE a LIKE 'M%';
> 2. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY a;
> 3. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b;
> 4. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b,a;
> 

Indices will be used to skip the sorting step in 2 and 3.
If you do this:

   DROP INDEX ib;
   CREATE INDEX ib ON t(b,a);

Then the new index ib will be used to sort in 4.

If you take the steps A, B, or C above, then the ia
index will be used to both sort and restrict the search
in query 2.

All of this you can discover for yourself by typing
in the query with the following prefix:

   EXPLAIN QUERY PLAN


--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] optimizer question

[Colin Manning]

Hi

If I have a table with a couple of indexed varchar fields e.g:

CREATE TABLE t (id INT, a VARCHAR(20), b VARCHAR(20)...);
CREATE INDEX ia ON t(a);
CREATE INDEX ib ON t(b);

then will the sqlite query optimizer use these indices in these SELECT's:

1. SELECT * FROM t WHERE a LIKE 'M%';
2. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY a;
3. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b;
4. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b,a;

...such that none of them will result in a table scan?

Thx



--
No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] optimizer question

[Colin Manning]

Hi

If I have a table with a couple of indexed varchar fields e.g:

CREATE TABLE t (id INT, a VARCHAR(20), b VARCHAR(20)...);
CREATE INDEX ia ON t(a);
CREATE INDEX ib ON t(b);

then will the sqlite query optimizer use these indices in these SELECT's:

1. SELECT * FROM t WHERE a LIKE 'M%';
2. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY a;
3. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b;
4. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b,a;

...such that none of them will result in a table scan?

Thx



--
No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21




-
To unsubscribe, send email to [EMAIL PROTECTED]
-