- 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
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
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?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.
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ść.
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 1) Zawsze wszystko robię na InnoDB. Chciałem spróbować jak to będzie na MyISAM 
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 
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 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.