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).
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