Ryan,
On Thu, Mar 7, 2013 at 4:31 PM, Ryan Johnson
<ryan.john...@cs.utoronto.ca> wrote:
> On 07/03/2013 5:59 PM, Igor Korot wrote:
>>
>> Ryan,
>>
>> On Thu, Mar 7, 2013 at 5:26 AM, Ryan Johnson
>> <ryan.john...@cs.utoronto.ca> wrote:
>>>
>>> Well, you *do* want an index for the target side of the join, but for FK
>>> joins the existing PK index already takes care of that.
>>
>> OK,
>>
>>> At this point you only need to add an index on d.id to make the d.id=1
>>> test
>>> fast. And yes, it will make updates predicated on d.id faster as well.
>>
>> Well, I just checked and the update is on both id and playerid.
>> Do I need the index on both fields?
>
> Ah. That gets a bit more tricky. You can only use one index per table, and
> whether any index on the table is useful depends on the order of its key
> columns and what you do in the query.
>
> If you always update based on equality, it doesn't matter whether you index
> (id,playerid) or (playerid,id). If one is equality and the other something
> else, put equality first (e.g. in your query you have id=1 and playerid=?,
> so the index should be on id,playerid).
Now, my update is:
UPDATE ... WHERE id = 1 AND playerid = {1,2,3,etc};
meaning that it an equal sign but the value depends on the other
record I am updating
in the table a with PK playerid.
Which means that I need an index based on the (id,playerid) as it does
not matter how I
put those fields, correct?
Thank you.
>
> If both are non-equality, put the index on which ever column you think will
> narrow down the results best, but at that point it's getting into black
> magic territory.
>
>
> Ryan
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
