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
– hgoebl, Commented Nov 14, 2013 at 7:43
@hgoebl, I'm using CODE tags for the formatting. Is there a better choice? — Igor
– Igor, Commented Nov 14, 2013 at 7: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
– hgoebl, Commented Nov 14, 2013 at 7:53
Because the meaning is different. One is player id and the other -league id. — Igor
– Igor, Commented Nov 14, 2013 at 8:16
This is misleading. Renaming to leagueid wouldn't hurt. And marking my answer as correct neither ;-) — hgoebl
– hgoebl, Commented Nov 14, 2013 at 8:31

Community · Accepted Answer · 2017-05-23 11:56:06Z

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.

Collectives™ on Stack Overflow

Creating index on the table

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related