Когда я должен использовать составной индекс?

Вы должны использовать составной индекс, когда используете запросы, которым он выгоден. Составной индекс, который выглядит следующим образом:

index( column_A, column_B, column_C )

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

index( column_A, column_B, column_C )
index( column_A, column_B )
index( column_A )

, но он не будет (по крайней мере, не напрямую, возможно, он может частично помочь, если нет лучших индексов) помочь запросам, которые нуждаются в

index( column_A, column_C )

. Обратите внимание, как отсутствует column_B.

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

Представьте, что у вас есть следующие три запроса:

Запрос I:

SELECT * FROM homes WHERE `geolat`=42.9 AND `geolng`=36.4

Запрос II:

SELECT * FROM homes WHERE `geolat`=42.9

Запрос III:

SELECT * FROM homes WHERE `geolng`=36.4

Если у вас есть отдельный индекс для столбца, все три запроса использовать индексы. В MySQL, если у вас есть составной индекс ( geolat , geolng ), только запрос I и запрос II (который использует первую часть составного индекса) используют индексы. В этом случае для запроса III требуется полный поиск по таблице.

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

На странице Справочного руководства MySQL :

Индекс с несколькими столбцами может быть считается отсортированным массивом, содержащим значения, которые созданы объединение значений индексированные столбцы .

Если вы используете разделенный индекс для столбцов geolat и geolng, у вас есть два разных индекса в вашей таблице, которые вы можете искать независимо.

INDEX geolat
-----------
VALUE RRN
36.4  1
36.4  8
36.6  2
37.8  3
37.8  12
41.4  4

INDEX geolng
-----------
VALUE RRN
26.1  1
26.1  8
29.6  2
29.6  3
30.1  12
34.7  4

Если вы используете составной индекс, у вас есть только один индекс для обоих столбцов :

INDEX (geolat, geolng)
-----------
VALUE      RRN
36.4,26.1  1
36.4,26.1  8
36.6,29.6  2
37.8,29.6  3
37.8,30.1  12
41.4,34.7  4

RRN - относительный номер записи (для упрощения можно сказать ID). Первые два индекса генерируются отдельно, а третий - составной. Как видите, вы можете выполнять поиск на основе геолокации на составном, поскольку он проиндексирован геолатом, однако можно искать по геолату или "геолату И географическому местоположению" (так как геолинг является индексом второго уровня).

Также посмотрите на Как MySQL использует индексы раздел руководства.

Нет черного и белого, один размер подходит для всех.

Вы должны использовать составной индекс, когда рабочая нагрузка по запросу выиграет от одного.

Вам необходимо профилировать рабочую нагрузку по запросу чтобы определить это.

Составной индекс вступает в игру, когда запросы могут быть полностью удовлетворены из этого индекса.

ОБНОВЛЕНИЕ (в ответ на редактирование опубликованного вопроса): если вы выбираете * из таблицы, составной индекс можно использовать, нельзя. Чтобы убедиться, вам нужно запустить EXPLAIN PLAN .

To do spacial searches, you need an R-Tree algorithm, which allows searching geographical areas very quickly. Exactly what you need for this job.

Some databases have spacial indexes built in. A quick Google search shows MySQL 5 has them (which looking at your SQL I'm guessing you're using MySQL).

Я с @Mitch, полностью зависит от ваших запросов. К счастью, вы можете создавать и удалять индексы в любое время, и вы можете добавить ключевое слово EXPLAIN к своим запросам, чтобы увидеть, использует ли анализатор запросов индексы.

Если вы будете искать точную широту / длинная пара этот индекс, вероятно, имеет смысл. Но вы, вероятно, будете искать дома на определенном расстоянии от определенного места, поэтому ваши запросы будут выглядеть примерно так (см. источник ):

select *, sqrt(  pow(h2.geolat - h1.geolat,  2) 
               + pow(h2.geolng - h1.geolng, 2) ) as distance
from homes h1, homes h2
where h1.home_id = 12345 and h2.home_id != h1.home_id
order by distance

, а индекс, скорее всего, не будет быть вообще полезным. Для геопространственных запросов вам понадобится что-то вроде this .

Обновление: с этим запросом:

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

Анализатор запросов может использовать только индекс geolat или только индекс geolng, или, возможно, оба индекса . Я не думаю, что он будет использовать составной индекс.