Are Compound Key Indexes Enough?

Question

This is what my table looks like:

CREATE TABLE pics( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, page INTEGER, w INTEGER, h INTEGER, FOREIGN KEY(page) REFERENCES pages(id) ON DELETE CASCADE, UNIQUE(name, page) ); CREATE INDEX "myidx" ON "pics"("page"); # is this needed? 

therefore, UNIQUE(name, page) must create an index. But is this index enough to make quick queries containing only the page field? How to select a set of "pics" WHERE page = ? . or JOIN pages.id ON pics.page ? Or do I need to create another index (myidx) for the page field only?

Answer 1

Think of a composite index as a phone book. The phone book is sorted by last name and then by first name. If you are given the name Bob Smith, you can quickly find section S, then Sm, then all Smith, and then Bob. This is fast because you have both keys in the index. Since the book is organized by first name, it would also be trivial to find all of Smith's notes.

Now imagine that you are trying to find all the people named Bob in the entire phone book. Much harder, right?

This is similar to how an index on a disk is also organized. Searching for all rows with a specific page column, when the list sorted in order (name, page) will basically lead to a sequential check of all rows, looking one after another at everything that has this page.

For more information on how indexes work, I recommend reading Use Index, Luke .

Answer 2

As indicated, you will need your other myidx index because your UNIQUE index first indicates name . In other words, it can be used to query:

• name
• name and page
• But not just the page .

Another option is to reorder the UNIQUE index and place the page column. Then it can be used only for page only requests, but will become incompatible with name only requests.

Answer 3

You need to analyze the query (s) that will use this table and determine which fields will be used to sort the results. You must index the fields that are used for larger sorting.