CODEWITHSUNDEEP
sqlsqliteindexing
user3129193
user3129193

Reputation: 61

SQLITE multiple column index vs. single column indices

Can somebody tell me if with the following SQLite statement..

CREATE TABLE products (
  category_id INT  NOT NULL, /* foreign key on `categories` */
  product_id  TEXT NOT NULL, /* must be string, unique only within a category */
  info        TEXT,
  PRIMARY KEY (category_id, product_id) /* our PK */
  ) WITHOUT ROWID

... if I should still construct following indexes

CREATE INDEX products_by_category_id
  ON products(category_id)
CREATE INDEX products_by_product_id
  ON products(product_id) /* not sure I need this one at all */

or if this is redundant somehow due to multiple column PK INDEX define in the CREATE TABLE

Upvotes: 2

Views: 816

Answers (1)

Markus Winand
Markus Winand

Reputation: 8706

Yes, it is redundant.

Index columns can be used from left to right. E.g. the PK index can be used with category_id alone or category_id and product_id together. Hence an index on category_id alone is useless while the one on product_id might be useful if you have queries that filter on product_id but not on category_id.

Upvotes: 2

Related Questions