Indeks kontra podzapytanie

Miałem wczoraj nie lada zagwostkę. Chciałem wcielić w życie kod z mojego faq numer danego wiersza. Potrzebne mi to było do wyliczania pozycji danego posta. Jakież było moje zdziwienie gdy się okazało, że podzapytanie nie uwzględnia założonych indeksów. To samo zapytanie wykonane normalnie - działało bez problemu, łykało wszystkie indeksy. Wystarczyło jednak wrzucić je do podzapytania i już leciało po wszystkich rekordach.

Ale po kolei. Oto moja tabela (wyrzuciłem zbędne pola by nie zaciemniać).

CREATE TABLE `f_post` (
  `ID` int unsigned NOT NULL auto_increment,
  `FK_TOPIC` int unsigned not null COMMENT 'Id tematu',
  `CNAME` varchar(128) default '' COMMENT 'Nazwa dodającego post',
  `POST` text not null COMMENT 'Tresc posta',
  `CDATE` datetime NOT NULL COMMENT 'Data utworzenia',
  `MDATE` datetime default null COMMENT 'Data modyfikacji',
  PRIMARY KEY  (`ID`),
  KEY `FK_TOPIC` (`FK_TOPIC`),
  KEY `CDATE` (`CDATE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Tabela posta';

Jak widać tabela postów ma między innymi założone indeksy na datę utworzenia oraz na id tematu. Zadanie jest proste: wyznaczyć pozycję posta o zadanym ID dla konkretnego tematu (posty sortowane są według daty utworzenia). W tym cely tworzymy zapytanie zgodnie ze wskazówkamu z faq:

SET @id=5,@nr = 0,  @idnr = 0; 
SELECT @idnr AS nr FROM  
(SELECT @nr:=@nr+1,IF(ID=@id, @idnr := @nr,@idnr=@idnr),ID FROM f_post WHERE FK_TOPIC=2 ORDER BY CDATE ASC) podsel WHERE podsel.ID = @id;

W tym przykładzie szukamy pozycji posta o ID 5 dla tematu o ID 2. Zapytanie działa poprawnie, znajduje to czego szukamy. Dla spokojności serca zrobiłem jednak EXPLAIN i co się okazało

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY<derived2>ALL (NULL) (NULL) (NULL) (NULL) 2 Using where
2 DERIVED f_post ALL FK_TOPIC FK_TOPIC 4 200216 Using filesort

Podzapytanie leci po wszystkich rekordach tak jakby nie uwzględniało indeksu na FK_TOPIC (pomimo, iż w explain pokazuje jakby uwzględniał). Wykonanie podzapytania jako zapytanie główne daje oczekiwany skutek - indeksy są brane pod uwagę.

SELECT @nr:=@nr+1,IF(ID=@id, @idnr := @nr,@idnr=@idnr),ID FROM f_post WHERE FK_TOPIC=2 ORDER BY CDATE ASC
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE f_post ref FK_TOPIC FK_TOPIC 4 const 12 Using where; Using filesort

Zamiast 200216 rekordów wziął do analizy tylko 12 - lekka różnica . Na podstawie prób i błędów okazało się, że jeżeli usunę z podzapytania sortowanie to łaskawie weźmie pod uwagę indeksy.

SET @id=5,@nr = 0,  @idnr = 0; 
SELECT @idnr AS nr FROM  
(SELECT @nr:=@nr+1,IF(ID=@id, @idnr := @nr,@idnr=@idnr),ID FROM f_post WHERE FK_TOPIC=2) podsel WHERE podsel.ID = @id;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 2 Using where
2 DERIVED f_post ref FK_TOPIC FK_TOPIC 4 12

Ale mnie to nie urządzało bo sortowanie mi tam było potrzebne. Po chwili zadumy, przerywanej przeglądaniem forum, skleciłem coś takiego:

ALTER TABLE f_post 
ADD KEY `FK_TOPIC_CDATE` (`FK_TOPIC`,`CDATE`)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 2 Using where
2 DERIVED f_post ref FK_TOPIC,FK_TOPIC_CDATE FK_TOPIC_CDATE 4 12 Using where

Strzał w dziesiątkę . Niestety muszę przyznać, iż to był naprawdę tylko strzał. Nie wiem czemu musiałem założyć kolejny indeks tym razem złożony z dwóch pól: FK_TOPIC oraz CDATE. Czemu bez tego indeksu zapytanie jako główne działa bez problemu, zaś jako podzapytanie nie uwzględnia właściwych indeksów? Może ktoś wie? Niestety nie dogrzebałem się nigdzie tej informacji.

No ale to nie był jeszcze koniec dnia. Przy okazji tego problemu wywiązała się miedzy mną a wookiebem dyskusja na temat sposobu wyliczania pozycji posta na forum. Wookieb zaproponował by do tego celu używać oddzielnego pola w tabeli postów, by nie trzeba było za każdym razem wyliczać tej pozycji. Zapraszam do lektury

Komentarze

 

2010-08-12 08:50 gość_matipl

2 fakty:
- order jest bardzo niewydajny
- indeksy powinniśmy optymalizować na takie jak używamy. Czyli jeśli w jednym zapytaniu wykorzystujemy 3 pola, to taki powinien być indeks, a nie walić osobno

2010-08-12 17:25 nospor

order jest bardzo niewydajny
Order by dla paru rekordów jest tak samo wydajny jakby go nie było. Postów w tematach raczej dużo mieć nie będę.

- indeksy powinniśmy optymalizować na takie jak używamy. Czyli jeśli w jednym zapytaniu wykorzystujemy 3 pola, to taki powinien być indeks, a nie walić osobno
Ale czemu dla zapytania głównego działa a podzapytania już nie?

2010-08-13 13:55 Michał

Dlaczego MyISAM a nie InnoDb? Mógłbyś nadać klucze obce, żeby jakiś dowcipniś nie narobił ci kuku w bazie, chyba, że sprawdzasz czy istnieje taki temat przy dodawaniu posta.

Zgaduję, że chodzi o szybkość. W takim razie można zastosować cache.

Czy moduły do twojego CMS mogą działać w kilku językach i na różnych silnikach bazy danych, np Sqlite, Mysql, Oracle?

Pozdrawiam

2010-08-13 13:58 Michał

PS Zapomniałem zapytać dlaczego używasz pola datetime, a nie pola int z liczbą sekund epoki uniksowej.

2010-08-13 19:05 nospor

Dlaczego MyISAM a nie InnoDb? Mógłbyś nadać klucze obce, żeby jakiś dowcipniś nie narobił ci kuku w bazie, chyba, że sprawdzasz czy istnieje taki temat przy dodawaniu posta.

1) Zawsze wszystko robię na InnoDB. Chciałem spróbować jak to będzie na MyISAM
2) Nie narobi mi kuku, tak sprawdzam wszystko
3) Tak, chodzi też o szybkość.

Czy moduły do twojego CMS mogą działać w kilku językach i na różnych silnikach bazy danych, np Sqlite, Mysql, Oracle?
Tak, mój cms jest dostosowany do kilku języków, moduły korzystają z tego samego mechanizmu co core więc i one mają wielojęzykowość.
Co do różnych silników: uzywam PDO więc tutaj już mam na dzień dobry lekkie wsparcie. Ale jakbym chciał przesiąść się na inną bazę to pare zmian w zapytaniach bym musiał zrobić.

PS Zapomniałem zapytać dlaczego używasz pola datetime, a nie pola int z liczbą sekund epoki uniksowej.
Pole INT służy do przechowywania liczba. Po to bazy danych wymyśliły typ datowy by z tego typu skorzystać. Jest szereg funkcji do operacji na takich polach, są one równie dobrze indeksowane więc dla czasu stosuję pola czasowe a nie liczbowe

2010-08-14 12:28 Michał

1) Zawsze wszystko robię na InnoDB. Chciałem spróbować jak to będzie na MyISAM 


Właśnie. Na pewno pomyślałeś o wyszukiwarce na swoim forum. Niestety na InnoDb nie da się zrobić wyszukiwania pełnoekstowego. A like z wydajnościowych przyczyn nie używamy..... Pozostaje zrobić tabelę indeks wyrazów ale przy dużym forum będą to miliony rekordów. (Tk jak to jest w phpBB).


Pole INT służy do przechowywania liczba. Po to bazy danych wymyśliły typ datowy by z tego typu skorzystać. Jest szereg funkcji do operacji na takich polach, są one równie dobrze indeksowane więc dla czasu stosuję pola czasowe a nie liczbowe 


Robiłeś testy ile zajmuje pole datetime a ile int unsigned? Ja takie rozwiązanie podpatrzyłem w bazie MyBB

Pozdro

2010-08-14 15:31 nospor

Niestety na InnoDb nie da się zrobić wyszukiwania pełnoekstowego.
Też to miałem na uwadze. Choć swego czasu robiłem taki myk przy innym projekcie: tabele InnoDB, a treść wyszukiwana dodatkowo do tabel MyISAM. Wyszukiwanie działało
Tutaj w przyszłości planuje podpiąć jakiś skrypt wyszukiwania np. Sphinx.

Robiłeś testy ile zajmuje pole datetime a ile int unsigned?
Nie, wyszedłem z założenia, że skoro baza udostępnia właściwe typy dla data, znaczy że jest ok.

2010-08-15 09:54 gość_cojack

Co do pola data trzymanego w typie INT jako linux epoch, to jest w tym sens, oczywiście szybsze będzie po nim order, ale upierdliwą rzeczą jest operacja na tych polach. Dlatego w większości przypadków stosuje się po prostu TIMESTAMP jako pole z datą, mniej roboty, mniej pierd*** się z tym wszystkim.

2010-08-15 12:00 Michał

@cojack: Tez tak myślę. W skrypcie ustawiam sobie format daty np. d-m-Y H:i i wrzucam to w funkcję date. A tak, żeby z pola Datetime wyciągnąć dzień tygodnia trzeba konwertować to na int strtotime.

Ciekawostką jest również to, że wszystkie adresy IP da się zapisać w postaci liczbowej. Dotychczas używałem pola varchar(15) A teraz jest to int Sql i php oferują do tego całkiem przystępne funkcje

Pozdrawiam

2010-09-13 01:48 gość_Kamil

Michał: miałem nieprzyjemność rozwijać projekt, w którym daty były zapisane w formacie liczbowym (INT). To wprowadzało mega syf i zamieszanie, gdy musiałem wykorzystać jakieś funkcje operujące na czasie.

Nie ma nic głupszego niż używanie znaczników i funkcjonalności wbrew ich przeznaczeniu! To tak jakby chcieć łyżką wbijać gwoździe

2010-09-29 22:03 gość_Tomasz Kowalczyk

Jeśli chodzi o samą ideę pobierania numeru wiersza z bazy danych, to polecam jeden z wpisów na moim blogu, a szczególnie dyskusję pod nim, zostało przedstawionych kilka bardzie ciekawych sposobów:

http://blog.kowalczyk.cc/2010/09/17/sql-zapytanie-pobierajace-kolejne-numery-zwracanych-rekordow/

Ja użyłem tego, który sam opisałem, bo tak jak zauważył autor wpisu, jest stosunkowo niewiele rekordów, więc optymalizacja jednej mikrosekundy nie ma sensu.

2010-09-29 22:11 nospor

Tomasz, ale to ma się nijak do przedstawionego tu problemu.
A to co ty przedstawiasz, to ja również opisałem już dawno u siebie:
http://nospor.pl/mysql-faq.html#faq-6

Dodaj komentarz

 

Dostępne bbcode: b, u, i, url, code, php, css, html, sql, js

Ostatnio komentowane

  1. Mysql - FAQ Paweł
  2. Pager 2.5.1 oraz EPa... Na szybko2
  3. Pager 2.5.1 oraz EPa... Sławek
  4. Mysql - FAQ Piotr
  5. Liczba dni roboczych Na szybko2
  6. Liczba dni roboczych Naszybko
  7. Klasa widoku nospor

Skrypty użytkowników

  1. Klasa obsługi szablo... Lirdoner
  2. Sekcje user76
  3. Klasa walidująca for... user76
  4. Licznik Gości online korey
  5. Form Builder Comandeer
  6. Dynamiczny licznik z... korey
  7. Captcha Comandeer