Re: AW: [sqlite] Bad performance with large joins?

[drh]

Jim Dodgen <[EMAIL PROTECTED]> wrote:
> 
> 2. get the optimizer to ignore indexes that I know cannot help
> 
>I do this in the where clause, by adding a zero or concatenating a "" 
> depending upon the data type
> 
> examples: 
> 
>  where sex = "M"
> changed to
> where sex||"" = "M"

It is more efficient to use a unary + operator, like this:

  where +sex = 'M'

The unary + is a no-op (no code is generated for it) but it
does disqualify the column it prepends to for use in an index
just like the || operator.  

Re: AW: [sqlite] Bad performance with large joins?

[Jim Dodgen]

As has been stated in the past SQLite is a small foot print RDBMS, one 
of the things keeping it small is that it does not have a large query 
optimizer.


One of the things that can happen is if you have lots of indexes the 
optimizer may pick something non optimum


My tricks are as follows:

1. reorder the from tables

2. get the optimizer to ignore indexes that I know cannot help

  I do this in the where clause, by adding a zero or concatenating a "" 
depending upon the data type


examples: 


where sex = "M"
   changed to
   where sex||"" = "M"

  where children = 2
 changed to
 where children+0 = 2
 





Steffen Schwigon wrote:

"Christian Schwarz" <[EMAIL PROTECTED]> writes:
  

Practically one such line for each table and each column.
  

Why on each column?



I just took the existing DB-import-script from that project. But ...


  

For example, when your where-clause contains columns A, B and C (in
this order) you should create *one* index on A, B and C.



... you are right, creating a combined index solves the speed problem.
Thanks.


  

Separate indexes on column A, B and C are not that useful. In this
case, SQLite would most probably use the separate index on column A.



Which would be a pity, wouldn't it? Postgres for instance seems to do
something more clever there, at least it's much faster, even with the
trivial setting of an index on each column.

Anyway, thanks for your answer.

GreetinX
Steffen 
  

Re: AW: [sqlite] Bad performance with large joins?

[Steffen Schwigon]

"Christian Schwarz" <[EMAIL PROTECTED]> writes:
>> Practically one such line for each table and each column.
>
> Why on each column?

I just took the existing DB-import-script from that project. But ...


> For example, when your where-clause contains columns A, B and C (in
> this order) you should create *one* index on A, B and C.

... you are right, creating a combined index solves the speed problem.
Thanks.


> Separate indexes on column A, B and C are not that useful. In this
> case, SQLite would most probably use the separate index on column A.

Which would be a pity, wouldn't it? Postgres for instance seems to do
something more clever there, at least it's much faster, even with the
trivial setting of an index on each column.

Anyway, thanks for your answer.

GreetinX
Steffen 
-- 
Steffen Schwigon <[EMAIL PROTECTED]>
Dresden Perl Mongers 

Re: AW: [sqlite] Bad performance with large joins?

[Steffen Schwigon]

"Christian Schwarz" <[EMAIL PROTECTED]> writes:
>> > Have you tried creating indexes on your rows.
>> > [..]
>> > I suggest you add indexes on text_val
>>
>> Yes. I use
>>
>>   create index text_val_idx on geodb_textdata(text_val);
>>
>
> This index seems pretty useless. You're querying against
> geodb_textdata.loc_id and geodb_textdata.text_type. So you should create
> an index over these columns.

Sorry, I just named this one in my reply.
In the DB there are much more indexes:

 [...]
 create index text_lid_idx on geodb_textdata(loc_id);
 create index text_val_idx on geodb_textdata(text_val);
 create index text_type_idx on geodb_textdata(text_type);
 create index text_locale_idx on geodb_textdata(text_locale);
 create index text_native_idx on geodb_textdata(is_native_lang);
 create index text_default_idx on geodb_textdata(is_default_name);
 create index text_since_idx on geodb_textdata(valid_since);
 create index text_until_idx on geodb_textdata(valid_until);
 [...]

Practically one such line for each table and each column.
If you want to see the whole db as import script, have a look at

  http://renormalist.net/opengeodb/opengeodb-sqlite.sql.gz

This DB was originally a Postgres one. I just changed the boolean
true/false into integer-0/1 and everything else at least syntactically
worked. Maybe I'm missing some other syntax that SQLite accepts but
silently ignores or handles differently.

GreetinX
Steffen 
-- 
Steffen Schwigon <[EMAIL PROTECTED]>
Dresden Perl Mongers