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