Index na często powtarzających się wartościach Opcje

[piotrek24]

W różnych kursach MySQL pisze że nie powinno się zakładać index-ów na kolumnach gdzie wartości często się powtarzają, ale... nie pisze dlaczego.

Przypuśćmy że mam tabelę "projekty" z takimi polami:

data - YEAR
projekt - VARCHAR

I mam tam powiedzmy 10000 rekordów z 3 lat. Czyli w tej liczbie 10000 są tylko trzy różne daty.

Wyświetlając projekty chcę najpierw wyświetlić listę lat:

[SQL] pobierz, plaintext 
mysql_query('SELECT data FROM projekty GROUP BY data');
[SQL] pobierz, plaintext 

Mam więc kolumnę z bardzo często powtarzającymi się rekordami, ale chyba powinienem nałożyć na nią index?.

[dr_bonzo]

A co ci daje indeks na tej dacie? ze baza potrafi czybko znalezc rekordy gdzie data == np. 2000.
Zaglada do poukladanego indeksu, znajduje szybko 2000, i znajduje 50000 rekordow z ta wartoscia, i teraz wszystkie wyciaga:
pozycja rekordu z indeksu, pobranie rekordu, pozycja kolejnego rekordu z indeksu, pobranie rekordu itd...
I wlasnie to [latanie w te i we wte] moze byc wolniejsze od Table Scan'u - przelecenia wszystkich rekordow, i dla kazdego sprawdzania czy data == 2000.
Zawsze mozesz porownac czasowo oba rozwiazania - to tylko indeks, nie napsuje ci bazy przy jego dodawaniu i usuwaniu.

[piotrek24]

Zrobiłem mały test. Dodałem do bazy 100 000 rekordów z losowo wstawionymi 5 datami (2000 - 2004)

[SQL] pobierz, plaintext 
mysql_query('SELECT data FROM projekty GROUP BY data');
[SQL] pobierz, plaintext 

Bez indeksu: 0,149s
Z indeksem: 0,000516s

[SQL] pobierz, plaintext 
mysql_query('SELECT data, projekt FROM projekty WHERE data = 2000 LIMIT 0, 50');
[SQL] pobierz, plaintext 

Bez indeksu: 0,006924s
Z indeksem: 0,001141s

Jak widać przy grupowaniu różnica jest ogromna, natomiast tak jak pisałeś przy pobieraniu rekordów różnica też jest, ale już nie taka duża.

Dalej jednak nie rozumiem dlaczego nie powinno się nakładać indeksów na kolumnach z powtarzającymi się rekordami?. Jaka jest różnica w operowaniu na tabeli która zawiera 100 tyś. zer i jedynek (0, 1) a tabeli która zawiera 100 tyś. niepoukładanych cyfr z zakresu 0 - 100 000 (234, 873, 1000, 99645... itd.).
Czemu według różnych kursów w pierwszym przypadku "nie opłaca się" zakładać index-u?, skoro zdaje się że w obu przypadkach założenie index-u daje takie same korzyści. A może się mylę?.

[osiris]

Jesli masz zalozony indeks na dana kolumne, to przy kazdym wstawianiu nowego rekordu, lub modyfikacji wartosci kolumny z zalozonym indeksem wartosc tego indeksu musi byc generowany od nowa oraz wstawiona do drzewa lub innej struktury w ktorej ten indeks jest przechowywany - a jest to czasochlonna operacja. Dlatego szczegolnie w tabelach w ktorych czesto modyfikujesz, dodajesz i usuwasz dane powinienes zwracac uwage na to czy nie masz tam niepotrzebnych (czasami zduplikowanych) indeksow.

Co do zapytania:

[SQL] pobierz, plaintext 
SELECT DATA FROM projekty GROUP BY DATA
[SQL] pobierz, plaintext 

jest ono szybsze z indeksem zalozonym na kolumnie data poniewaz dane zostana pobrane z samego indeksu - dostep do tabeli "projekty" wogole nie jest potrzebny.

Natomiast w zapytaniu:

[SQL] pobierz, plaintext 
SELECT DATA, projekt FROM projekty WHERE DATA = 2000 LIMIT 0, 50
[SQL] pobierz, plaintext 

roznica dla przypadku z zalozonym indeksem i bez tego indeksu nie jest juz tak duza, gdyz tutaj pobieramy jeszcze wartosc z kolumny projekt, a do tego potrzebujemy "przejechac" sie po tabeli.