Indexy - jak stosować? Opcje

[phpion]

Hejka,
aż wstyd się przyznać ale do tej pory nie zaprzątałem sobie tym głowy i robiłem indexy na pojedyczne kolumny. Chciałbym się jednak dowiedzieć czy jest sens robienia czegoś takiego:
Mam tabelę, w której są pola np.
- login varchar
- haslo varchar
- aktywny enum("0", "1")
Indexy stawiałem dla każdego z pól osobno. Jednak najczęściej tworzę zapytanie, w którym nie wybieram rekordu po 1 warunku (np. WHERE login="nanana") tylko 3 na raz (WHERE login="nanana" AND haslo="ooo" AND aktywny="1"). Czy w takim przypadku nie powinienem utworzyć również indexu:
INDEX (login, haslo, aktywny)
? Jeśli tak to to by oznaczalo, że należy tworzyć indexy na wszystkie kombinacje danych?
Pozdrawiam: pion

[JaRoPHP]

Jeśli tak to to by oznaczalo, że należy tworzyć indexy na wszystkie kombinacje danych?

Na pewno nie - pamiętaj, że indeksy zajmują miejsce, często bardzo dużo miejsca (nie raz zdarzało się, że ponad 70% miejsca na dysku zajmowały nie dane, a indeksy). Nie ma sensu tworzyć nadmiarowych indeksów.

Duża liczba indeksów jest uzasadniona w sporadycznych przypadkach, zawsze jednak w momencie napotkania tabel wymagających dużej liczby indeksów warto ponownie rozważyć poprawność projektu.

Kiedy np. nie ma sensu stosować indeksów:
- gdy trzeba przeszukać całą tabelę (np. aby zliczyć, ilu użytkowników ma status aktywny, a ilu nieaktywny),
- gdy wyszukiwana wartość w kolumnie jest działaniem jakiejś funkcji (np. substr).

W Twoim przypadku dałbym indeks złożony na kolumny: login, haslo, aktywny. Należy pamiętać, że będzie on także użyteczny, gdy będziesz wyszukiwał tylko wg loginu, bądź tylko według loginu i hasła (porównania oczywiście muszą być w tej samej kolejności, w jakiej tworzą indeks).

Indeks złożony jest w pełni użyteczny nawet w przypadku, gdy nie są określone niektóre kolumny klucza, pod warunkiem że są określone wszystkie kolumny z początku klucza

// Cytaty
"SQL. Sztuka programowania." /S. Faroult, P.Robson/

[phpion]

W Twoim przypadku dałbym indeks złożony na kolumny: login, haslo, aktywny. Należy pamiętać, że będzie on także użyteczny, gdy będziesz wyszukiwał tylko wg loginu, bądź tylko według loginu i hasła (porównania oczywiście muszą być w tej samej kolejności, w jakiej tworzą indeks).

O taką informację mi chodziło, dzięki serdeczne!

// EDIT:
Jednak coś jeszcze nie daje mi spokoju. Mając taką tabelę:

[SQL] pobierz, plaintext 
CREATE TABLE property (
	id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
	region_id TINYINT(2) UNSIGNED NOT NULL,
	standard TINYINT(1) UNSIGNED NOT NULL,
	coord_x SMALLINT(3) UNSIGNED NULL,
	coord_y SMALLINT(3) UNSIGNED NULL,
	is_promoted ENUM ("0", "1") DEFAULT "0",
	is_luxury ENUM ("0", "1") DEFAULT "0",
	is_active ENUM ("0", "1") DEFAULT "1",
 
	PRIMARY KEY (id),
 
	CONSTRAINT FOREIGN KEY (region_id) REFERENCES region (id) ON DELETE CASCADE,
 
	INDEX (region_id, is_active),
	INDEX (standard),
	INDEX (is_luxury)
) Engine=InnoDB DEFAULT CHARSET=UTF8;
[SQL] pobierz, plaintext 

jak powinieniem założyć indexy? Dane będą zawsze (!) wyszukiwane po region_id oraz is_active oraz opcjonalnie jeśli user wybierze standard to po standardzie oraz jeśli zaznaczy is_luxury to po tym polu.
Z góry dzięki za info.
pion

Ten post edytował phpion.com 16.12.2007, 18:46:17

[JaRoPHP]

Dane będą zawsze (!) wyszukiwane po region_id oraz is_active oraz opcjonalnie jeśli user wybierze standard to po standardzie oraz jeśli zaznaczy is_luxury to po tym polu.

Rozumiem, że są możliwe następujące sytuacje wyszukiwania:
- po polach: region_id, is_active
- lub po polach: region_id, is_active, standard
- lub po polach: region_id, is_active, is_luxury
- lub po polach: region_id, is_active, standard, is_luxury.

W takiej sytuacji spróbowałbym z indeksami:

[SQL] pobierz, plaintext 
INDEX (region_id, is_active, standard)
INDEX (is_luxury)
[SQL] pobierz, plaintext 

Moim zdaniem, nie ma sensu tworzyć osobnego indeksu dla pola standard...

[phpion]

Ok, chyba zaczaiłem jak to działa. Dzięki jeszcze raz

[DeyV]

porównania oczywiście muszą być w tej samej kolejności, w jakiej tworzą indeks).

Hmm - a to dlaczego?
Przecież dla optymalizatora zapytań nie ma znaczenia kolejność warunków w WHERE

W Twoim przypadku dałbym indeks złożony na kolumny: login, haslo, aktywny.

A ja zrezygnowałbym z hasła zarówno w Index, jak i w zapytaniu.
W indexie nie jest potrzebne, bo jak sądzę, masz bazę gdzie Login jest Unique, więc i tak nie pojawi się nigdy kombinacja gdzie będzie 1 login i wiele haseł, stąd też index na haśle nigdy nie będzie wykorzystany.

A w zapytaniu unikałbym tego z względów czystego bezpieczeństwa - wiele zapytań jest logowanych, często wyświetlane są w trakcie debugowania, tak więc szkoda by było otwarcie wyświetlić tą informację.

[JaRoPHP]

Hmm - a to dlaczego?
Przecież dla optymalizatora zapytań nie ma znaczenia kolejność warunków w WHERE

Z tą kolejnością jednak byłbym ostrożny. Niby nie ma znaczenia, jednak optymalizatory bywająz awodne i nie zawsze wybierają najszybszą drogę zapytania. Tutaj odsyłam do podanej wyżej literatury.