Witam.

Mam pytanie odnośnie indeksacji tabel.
Mam tabelę "languages" : id (int),name (varchar) ,iso (varchar) ,sequence (int).

Przypuśćmy, że wykonuję na niej dwa zapytania:
SELECT `Language`.`id`, `Language`.`iso`, `Language`.`desc`, `Language`.`sequence` FROM `languages` AS `Language` WHERE 1 = 1 ORDER BY `sequence` asc

SELECT `Language`.`id`, `Language`.`iso`, `Language`.`desc`, `Language`.`sequence` FROM `pmw_languages` AS `Language` WHERE id = 1

Jakie powinny być indeksy dla tej tabeli? Czy indeksować pola po klauzuli WHERE, czy klauzula ORDER też ma znaczenie?
Tzn. czy wystarczy indeks PRIMARY KEY na id, aby stwierdzić, że tabela jest poprawnie zaindeksowana? Czy należy jeszcze założyć INDEX na pole 'sequence' po którym najczęściej sortuję.

To samo pytanie tyczy się pola w innej tabelce (tabela "photos") - pole `created` (datetime). Czyli data dodania rekordu. Jeśli ów tabelę sortuję najczęściej po owym "created" to czy też powinien on być uwzględniony w indeksie? Np dla najczęściej wykonywanego zapytania:
SELECT `Photo`.`id` FROM `photos` AS `Photo` WHERE category_id = 1 ORDER BY `created` desc
Czy poprawny indeks dla tej tabeli to sam INDEX category_id, czy INDEX złożony z dwóch pól, a mianowicie `category_id` i `created`?

Z góry dziękuję za pomoc:)

Kluczowym elementem mogącym pomóc w podjęciu decyzji jest "po którym najczęściej sortuję". Jeżeli tak faktycznie jest to nadanie indeksu na tą kolumnę będzie dobrym posunięciem.


Wydaje mi się, że najlepszym rozwiązaniem będą w tym przypadku 2 osobne indeksy. Indeks złożony z dwóch kolumn przydałby Ci się gdybyś sortował (ORDER BY) lub filtrował (WHERE) dane po tych dwóch kolumnach (czyli np. ORDER BY category_id, created) natomiast w przypadku gdy najpierw filtrujesz dane wybierając tylko te z określonej kategorii, a dopiero potem je sortujesz lepszym rozwiązaniem okażą się 2 osobne indeksy.

W przypadku gdy najczęściej stosowałbyś sortowanie malejące dobrze by było użyć indeksu malejącego ale niestety:

Wygląda na to, że nawet w wersji 6.0 tego nie wprowadzili - a szkoda. Potraktuj to więc jako ciekawostkę hehe

Witam.
Dziękuję za rzeczową odpowiedź. Niech sprawdzę, czy dobrze zrozumiałam: nacisk przy projektowaniu indeksacji należy położyć na kolumny, po których sortujemy (ORDER), a także po tych poprzez które wyszukujemy (WHERE). Indeksy złożone z większej ilości niż 1 pole mają sens raczej wtedy, kiedy razem występują w klauzuli WHERE bądź ORDER. W innym przypadku np. na przykładzie dwa pola w WHERE, jedno w ORDER - należy założyć osobny indeks na 2 pola WHERE i osobny na pole ORDER.
Natomiast jak to się ma w momencie klauzuli JOIN? Wiadomo, że pole łączące dwie tabele, powinno posiadać INDEX. Natomiast np. dla poniższego zapytania:
SELECT * from photos left join users on photos.user_id=users.id where category_id=4 order by sequence
Rozumiem, że należy założyć trzy osobne indeksy na pola: user_id, category_id, sequence?

Jeszcze jedno pytanie: wyczytałam, iż zbyt duża ilość indeksów również negatywnie może wpływać na pracę aplikacji. Czy należy indeksować wszystkie pola, z których korzystamy w danej tabeli w WHERE i ORDER, czy np. te które są rzadko używane (np. w 2-3 funkcjach z poziomu panelu administratora, używanych o wiele rzadziej niż funkcje z front-endu) - pozostawić niezaindeksowane?

Nie znalazłam w sieci wątku, który jednoznacznie określa sposób myślenia podczas projektowania indeksów w rozbudowanych aplikacjach.
Może ten wątek komuś pomoże:-)
Pozdrawiam,
chrupka

Ogolnie indexy pomagaja w wyszukiwaniu ( czyli SELECT ) ale zwalniaja przy (INSERT, UPDATE, DELETE..).
Jesli robisz duzo pobran z bazy to duzo indexow;) (ale bez przesady).

Innym sposobem na sprawdzenie, czy dobrze masz zalozone indexy jest EXPLAIN .. i tu zapytanie

SELECT `Photo`.`id` FROM `photos` AS `Photo` WHERE category_id = 1 ORDER BY `created` desc


Czy jesteś pewien?
Np. dla tabeli "catalogs" : id (int PRIMARY KEY), user_id (int), sequence (int)

i dla podobnego jak wyżej zapytania:

[SQL] pobierz, plaintext 
SELECT Catalog.id FROM catalogs AS Catalog WHERE user_id=4 ORDER BY Catalog.sequence ASC
[SQL] pobierz, plaintext 

Stworzyłam najpierw dwa odrębne klucze: jedno na pole user_id i drugie na pole sequence
EXPLAIN tego zapytania pokazał, że SQL mógł skorzystać z klucza user_id i tylko z tego skorzystał

Stworzyłam trzeci klucz, zawierający oba te pola (user_id i sequence) i EXPLAIN pokazał, że SQL mógł skorzystać z klucza user_id oraz user_sequence. I skorzystał z user_sequence.