Достаточно ли индексов из составных ключей?

Вот так выглядит моя таблица:

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?

поэтому UNIQUE(name, page) должен создать индекс. Но достаточно ли этого индекса для выполнения быстрых запросов, использующих только поле page? Как выбрать набор "фото" WHERE page = ?. или JOIN pages.id ON pics.page ? Или я должен создать другой индекс (myidx) только для поля страницы?


sql sqlite performance indexing composite-key
person Alex    schedule 06.08.2013    source источник
comment
В любом составном индексе он может использоваться, если ваш запрос использует n крайних левых столбцов (где n находится между 1 и количеством столбцов в вашем индексе). В вашем случае, если ваш запрос использует name или name и page, он может быть использован - если ваш запрос относится только к page, этот составной индекс определенно бесполезен.   -  person marc_s    schedule 06.08.2013

Ответы (3)


arrow_upward
3
arrow_downward

Думайте о составном индексе как о телефонной книге. Телефонная книга сортируется по фамилии, а затем по имени. Если вам дано имя Боб Смит, вы можете быстро найти раздел S, затем Sm, затем весь раздел Smith< /em>, а затем Боба. Это быстро, потому что у вас есть оба ключа в индексе. Так как книга упорядочена сначала по фамилии, было бы так же просто найти все записи Smith.

Теперь представьте, что вы пытаетесь найти всех людей по имени Боб во всей телефонной книге. Гораздо сложнее, правда?

Это аналогично организации индекса на диске. Поиск всех строк с определенным столбцом page, когда список отсортирован в порядке (name, page), в основном приведет к последовательному сканированию всех строк, поиску одной за другой всего, что имеет эту page< /эм>.

Для получения дополнительной информации о том, как работают индексы, я рекомендую прочитать Используйте индекс, Люк.

person Mike Christensen    schedule 06.08.2013

arrow_upward
6
arrow_downward

Как уже говорилось, вам понадобится другой индекс myidx, потому что ваш индекс UNIQUE сначала указывает name. Другими словами, его можно использовать для запроса:

  1. name
  2. name и page
  3. Но не только page.

Другой вариант — переупорядочить индекс UNIQUE и сначала разместить столбец page. Затем его можно будет использовать только для page запросов, но он станет несовместимым только с name запросами.

person Michael Goldshteyn    schedule 06.08.2013

arrow_upward
2
arrow_downward

Вам необходимо проанализировать запросы, которые будут использовать эту таблицу, и определить, какие поля будут использоваться в запросе для сортировки результатов. Вы должны индексировать поля, которые используются для сортировки больше всего.

person kkuilla    schedule 06.08.2013
comment
Для сортировки я могу использовать w и h, у которых нет индексов:/Если то, что вы говорите, верно, мне придется добавить индексы для всех полей в моей таблице. - person Alex; 06.08.2013
comment
Майк Кристенсен говорит то, что я хотел сказать. - person kkuilla; 06.08.2013