Okay, thanks! I think I understand better now, how to proceed.
Regards,
Jonas
On Fri, Nov 27, 2009 at 3:19 AM, Igor Tandetnik wrote:
> Jonas Sandman wrote:
>> Yes, but considering that I first join on typeid and then have name
>> and subspecies in the where, wouldn't that index be optimal for th
Jonas Sandman wrote:
> Yes, but considering that I first join on typeid and then have name
> and subspecies in the where, wouldn't that index be optimal for that
> query?
Again, the order of conditionals in the query is largely irrelevant. Remember,
your statement is equivalent to this one:
sele
Yes, but considering that I first join on typeid and then have name
and subspecies in the where, wouldn't that index be optimal for that
query?
Jonas
On Thu, Nov 26, 2009 at 4:42 PM, Igor Tandetnik wrote:
> Jonas Sandman wrote:
>> Doesn't that mean that my original suggestion is correct then?
>>
Jonas Sandman wrote:
> Doesn't that mean that my original suggestion is correct then?
>
> create index idx_animals on animals(typeid, name, subspecies)
>
> as those three columns of the animals table are used in the select?
For this particular query, and for one particular way of executing this
I see!
Doesn't that mean that my original suggestion is correct then?
create index idx_animals on animals(typeid, name, subspecies)
as those three columns of the animals table are used in the select?
Regards,
Jonas
On Thu, Nov 26, 2009 at 4:05 PM, Igor Tandetnik wrote:
> Jonas Sandman wrote:
Jonas Sandman wrote:
> But I guess the answer is that only the "where" parts should be
> indexed, not the id's in the joins?
No, that's generally not true. ON clauses in joins are basically a syntactic
sugar (though there's a subtle difference in case of outer joins). These three
statements are
Yes, I forgot to add "at.name as AnimalType" in the select.
It's not a real-life sample, it was just a way to try to describe what
I am thinking about...
But I guess the answer is that only the "where" parts should be
indexed, not the id's in the joins?
/Jonas
On Thu, Nov 26, 2009 at 3:38 PM, I
Jonas Sandman wrote:
> If you have a join in an SQL-query, how do you add the statements to
> optimize the query in an index?
>
> For example:
>
> select a.* from animals a
> join animaltype at on at.id=a.typeid
> where a.name='Monkey' and a.subspecies=2
Why are you joining with animaltype here?
On 26 Nov 2009, at 11:02am, Jonas Sandman wrote:
> If you have a join in an SQL-query, how do you add the statements to
> optimize the query in an index?
Create good indexes. SQLite uses its own cleverness to pick which of the
available indexes is the best one. It is much better at picking th
Hello,
If you have a join in an SQL-query, how do you add the statements to
optimize the query in an index?
For example:
select a.* from animals a
join animaltype at on at.id=a.typeid
where a.name='Monkey' and a.subspecies=2
do I add the index like this: "create index idx_animals on
animals(typ
10 matches
Mail list logo
