Jef Driesen <[EMAIL PROTECTED]> wrote:
Is there a difference between using a primary key or another index for a join?
There is a slight difference. When SQLite looks up by ROWID (which INTEGER PRIMARY KEY column maps to) it jumps directly to the record, and has all the fields in it easily available. When looking up by any other index, the engine finds an entry in the index which contains all the fields comprising the index, as well as the ROWID of the record in the table. If the query nees some columns in the record that are not contained in the index, SQLite has to perform another lookup, by ROWID in the table.
In your particular query, all the necessary data is in the index and the second lookup is not necessary. So you won't see any difference.
And what is the advantage (if there is one) over not using a join at all: SELECT name, (SELECT count (*) FROM events WHERE events.place_id = places.id) AS count FROM places;
None. I expect the execution plan to be almost identical for these two statements. You still need an index on events(place_id) if you want this query to run fast.
Igor Tandetnik
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
