Creating index on the table

Question

Everywhere I look it is explained that I should create a table index in situations like this:

SELECT * FROM foo, bar WHERE foo.field1 < <some_value>;

However my situation is different.

I am using SQLite and have the following schema:

CREATE TABLE leagues(
    id integer primary key,
    ...
);
CREATE TABLE players(
    playerid integer, 
    id integer, 
    type integer, 
    value double, 
    PRIMARY KEY(playerid,id)
);
CREATE TABLE scoretype(
    scoreid integer primary key, 
    scorename varchar(50)
);
CREATE TABLE leaguescoretype(
    playerid integer,
    id integer,
    scoreid integer, 
    value double, 
    foreign key(playerid) references players(playerid), 
    foreign key(id) references leagues(id), 
    foreign key(scoreid) references scoretype(scoreid)
);

and I want to run following query:

SELECT 
    players.playerid, 
    scoretype.scorename, 
    leaguescoretype.value 
FROM players,scoretype,leaguescoretype 
WHERE scoretype.scoreid = leaguescoretype.scoreid 
AND players.playerid = leaguescoretype.playerid 
AND players.playerid = 1 AND players.id = 1;

Trying to run this query under EXPLAIN QUERY PLAN I am getting:

SCAN TABLE leaguescoretype (~1000000 rows)
SEARCH TABLE players USING COVERING INDEX sqlite_autoindex_players(id=? AND playerid=?) (~1 rows)
SEARCH TABLE scoretype USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

Is there a way to improve the performance of this query?

you could do us a favor and reformat the code so we don't have to scroll all the time. — hgoebl, Nov 14 '13 at 07:43
@hgoebl, I'm using CODE tags for the formatting. Is there a better choice? — Igor, Nov 14 '13 at 07:45
Is there a reason why you have two IDs in players? Is one of them unique and known at the time of insert? — hgoebl, Nov 14 '13 at 07:53
Because the meaning is different. One is player id and the other -league id. — Igor, Nov 14 '13 at 08:16
This is misleading. Renaming to `leagueid` wouldn't hurt. And marking my answer as correct neither ;-) — hgoebl, Nov 14 '13 at 08:31

score 1 · Accepted Answer · edited May 23 '17 at 11:56

CREATE INDEX ix_lst ON leaguescoretype (playerid)

should help I suppose.

If you often additionally search by scoretype (with equal-check), you could create it like that:

CREATE INDEX ix_lst ON leaguescoretype (playerid, scoreid)

Have a look at Foreign key optimization in SQLite

Edit

BTW I'd remove one of (playerid, id) from the primary key of table players. The foreign key is formally (or at least cosmetically) not correct, because it only references one of the PK columns. Though I think this doesn't hurt performance.

Edit 2 Most relational databases do not automatically create indexes for foreign key constraints. And in many cases this is not necessary.

Take your table scoretype as an example. I assume that the only actions on this table are inserts which are very rare. If you never search your leaguescoretype primarily by scoretypeid, then you don't need an index.

But without index, rows in the table can only be found by scanning the whole table. Look at the where conditions and on the size of the tables. Larger tables benefit from indexes. (This is generally speaking and not valid in all cases.)

Yes, it does help. Thank you. Now could you explain the reasoning for such an index. I don't like to use something I don't understand. TIA. — Igor, Nov 14 '13 at 08:15

Creating index on the table

1 Answers1