indeks - jaki najbardzij optymalny?

pytanie nurtuje mnie od dłuższego czasu, nawet specjalnie poszedłem na kurs baz danych, żeby poznać pewną odpowiedź, ale jak na złość akurat na tym jednym wykładzie, na którym to było omawiane, nie mogłem być :/

Problem dotyczy konkretnie bazy MySQL i na przykład takiej tabeli "komentarze":

• id_komentarza (klucz główny)
• id_komentowanego_artykulu
• wartosc_komentarza
• treść itp.

chcę pobrać z bazy komentarze do danego artykułu, posortować je według wartości i wyświetlić pierwsze kilka, widzę dwa rozwiązania:

• indeks na obie kolumny na raz - na pewno przyspieszyło by to selecta na obie te kolumny, ale czy na pewno przyspieszy select na jedną i sort po drugiej?
• indeks na każdą z osobna - na pewno przyspieszy to select, ale czy nie zignoruje wtedy drugiego indeksu?

Prosiłbym o pewne odpowiedzi - strategię "im więcej indeksów tym lepiej" znam, potrzebuję czegoś z głową ;). Najfajniej by było, jakby ktoś poparł stwierdzenie "tamto jest szybsze" jakimś wyjaśnieniem dlaczego. Problem nie dotyczy tylko wspomnianych komentarzy, ogólnie zderzam się z nim co jakiś (krótki) czas, więc takich danych do posortowania może być dużo.

Zrób indeks tylko na polu id_komentowanego. Ile taki artykuł może mieć komentarzy? 1000? posortowanie takiej ilości danych w pamięci to żadne obciążenie dla bazy, a będziesz miał mniejszy indeks - mniej danych do odczytania z dysku, co się na pewno opłaca.

to był tylko przykład mający przybliżyć istotę problemu (niestety na chwilę obecną ciężko mi podać konkretny), może podam trafniejszy:

jest sobie baza zamówień, a najczęściej wykonywaniem zapytaniem jest "nasze największe zamówienia" w danej kategorii - select po id kategorii (kategorii może być powiedzmy 100) + order po wartości zamówienia (powiedzmy 1 mln zamówień równomiernie rozłożonych po kategoriach) i limit 5

A EXPLAIN nie podpowiada jakich indeksow uzywa silnik w danym zapytaniu?

W takim razie indeks na (id_kategorii, wartość). Przyzwoita baza danych (nie wiem , jak to jest z mysql) powinna wykorzystać ten indeks do sortowania. Dwa indeksy - raczej nie zadziała (no bo jak miałoby to działać? Indeks to żadna magia, jest to zwyłe drzewo n-arne).
Przyłączam się do rady kolegi - używaj explain.

Tak jak marcin_mank - jedyny problem może być jednak taki, że system może nie załapać, że na pierwszej kolumnie jest warunek równościowy i że może wtedy nie sortować danych, bo w ogólnym przypadku gdybyś w warunku na pierwszej kolumnie dał cokolwiek innego niż "=", to druga kolumna już nie musi być posortowana. Dlatego EXPLAIN dobrze użyc i zobaczyć, z jak inteligentnym RDBMSem ma się do czynienia.

BTW. Użycie indeksu do sortowania opłaca się tylko wtedy, gdy wybierasz mało rekordów, czyli np. masz mały LIMIT albo bardzo selektywny warunek na pierwszej kolumnie. Powyżej pewnej liczby rekordów (wcale nie takiej wielkiej - w setkach, może tysiącach), opłaca się sortować wszystko zamiast używać indeksu, o ile mówimy o zwykłym indeksie, niegrupującym (non clustered). Koszt losowego skakania nawet po kilku % tabeli na dysku (tabela nie jest posortowana) może być znacznie większy niż pełny skan i sekwencyjne sortowanie wszystkiego.

A jaki wykład przegapiłeś? Jeśli na WEiTI w tym semestrze, to ja go prowadziłem :D

no bo jak miałoby to działać? Indeks to żadna magia, jest to zwyłe drzewo n-arne

No zwykłe jak zwykłe. N-arne to ono raczej nie jest, bo liczba gałęzi w każdym węźle jest zmienna, a dodatkowo jest równoważone tak aby liczba poziomów drzewa wynosiła zawsze 2-4 i była jednakowa dla całego drzewa (liście są tylko na jednym poziomie). Poza tym indeksy mogą mieć bardzo różną postać i w ogóle nie muszą być drzewami. W tym przypadku jeśli RDBMS udostępnia wiele typów indeksów to należy pamiętać, żeby wybrać własnie B+tree, a nie np. indeks mieszający (hash) czy bitmapowy, bo one na sortowanie niewiele się przydadzą.

wielkie dzięki, właśnie takiego wytłumaczenia szukałem :)

A wykład to akurat na uni wrocławskim - zwykły (coroczny) przedmiot