index podzapytania Opcje

[nospor]

Hejka, mam taką zagwostkę:
Jest sobie zapytanie:

[SQL] pobierz, plaintext 
SELECT @nr:=@nr+1,IF(ID=@id, @idnr := @nr,@idnr=@idnr),ID FROM f_post WHERE FK_TOPIC=2 ORDER BY f_post.ID ASC
[SQL] pobierz, plaintext 

No i ładnie mi bierze index FK_TOPIC.
Explain:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1 SIMPLE f_post ref FK_TOPIC FK_TOPIC 4 const 2 Using where; Using filesort

Wziął mi pod uwagę dwa rekordy.

Daję teraz to w podzapytanie:

[SQL] pobierz, plaintext 
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 f_post.ID ASC ) podsel WHERE podsel.ID = @id;
[SQL] pobierz, plaintext 

I już mi nie uwzględnia FK_TOPIC jako klucza

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1 PRIMARY <derived2>ALL(NULL)(NULL)(NULL)(NULL)2Using where
2 DERIVED f_post ALL FK_TOPIC FK_TOPIC 4 118 Using filesort

Bierze pod uwagę wszystkie rekordy (118)

O co chodzi?Mam chwilowe zaćmienie związane z poszukiwaniem samochodu (IMG:style_emoticons/default/smile.gif)

ps: wystarczy ze wywalę ORDER BY ID asc i już gra jak ta lala, szuka po dwóch rekordach.
Czemu normalnie dziala niezależnie od order by, a w podzapytaniu trzeba wywalic order by by poszło normalnie

[cojack]

Bo orderby nie ma sensu stosować w subquery, chyba że byś tam jakiś limit strzelił ale go nie ma. Dodaj orderby na końcu całego zapytania i będzie działać.

[wookieb]

Trudno testować bez struktury tabel :/
Jaka wersja Mysql?

DLaczego tak się stało? Możliwe, że z tego powodu iż w pierwszym zapytaniu nie ma zdefiniowanego @id. Optymalizator wyłapuje takie rzeczy i może wywalić takiego ifa. Użyj Explain extended aby zobaczyć różnicę.

[nospor]

@cojak to że ty sensu nie widzisz nie znaczy że go nie ma (IMG:style_emoticons/default/winksmiley.jpg)
Tutaj jak najbardziej jest sens. A danie tego order by na koncu da zupelnie inny wynik niż oczekiwany.

O to całe zapytanie:

[SQL] pobierz, plaintext 
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 ID ASC) podsel WHERE podsel.ID = @id;
[SQL] pobierz, plaintext 

EXPLAIN EXTENDED :

Kod

id    select_type    table    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1    PRIMARY    <derived2>    ALL    null                  null       null     null    2    100.00    Using where
2    DERIVED    f_post    ALL    FK_TOPIC    FK_TOPIC    4        118    1.69    Using filesort

a o to struktura tabeli (wywalilem niepotrzebne pola):

[SQL] pobierz, plaintext 
CREATE TABLE `f_post` (
  `ID` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `FK_TOPIC` int UNSIGNED NOT NULL COMMENT 'Id tematu',
  `CDATE` datetime NOT NULL COMMENT 'Data utworzenia',
  `ACTIVE` tinyint NOT NULL DEFAULT 1 COMMENT '0 - nieaktywny, 1 - aktywny',
  PRIMARY KEY  (`ID`),
  KEY `FK_TOPIC` (`FK_TOPIC`),
  KEY `CDATE` (`CDATE`),
  KEY `ACTIVE` (`ACTIVE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Tabela posta';
 
[SQL] pobierz, plaintext 

edit:
rozwiązaniem było założenie indexu na dwa pola jednoczesnie: na to po którym szukamy i na to po którym sortujemy

[SQL] pobierz, plaintext 
ALTER TABLE f_post
ADD KEY CDATE2 (FK_TOPIC,CDATE);
[SQL] pobierz, plaintext 

Dałem CDATE gdyż zmieniłem sortowanie na date a nie ID

[SQL] pobierz, plaintext 
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;
[SQL] pobierz, plaintext 

[wookieb]

A ja zadam inne pytanie. Co stoi na przeszkodzie aby do tabeli postów dodać kolumnę position oznaczającą pozycję postu w temacie?

[nospor]

@wookieb bo widzisz, zależy jak leży (IMG:style_emoticons/default/winksmiley.jpg)

No to dodajmy to pole, co się teraz dzieje:
mamy pole POSITION.
Dodajemy nowy post, sprawdzamy jaka jest pozycja ostatniego i dla nowego ustalamy o 1 większą. Ok
Usuwamy post ze środka - musimy wyliczyć na nowo pozycje dla wszystkich postów leżących za usuniętym - też żaden problem.
Chcemy pobrać pozycję posta - jedno proste zapytanie bez babrania się w podzapytania.
=
Same plusy.

Ale......
co, gdy w przyszłości będę chciał rozwinąć forum i dodać np. możliwość okreslania przez użytkowników, w jakiej kolejności chcą wyświetlać posty (rosnąca,malejąca)? Nie będę mógł już skorzystać z POSITION, bo ono będzie tylko dla ASC. A użytkownik wybierze sobie DESC i zonk. Rozwiązaniem byłoby dodanie pola POSITION_DESC.

W sumie to też nie głupi pomysł.
Widzicie jeszcze jakieś przypadki, gdzie POSITION było by złe? jakieś inne wyświetlanie postów? Zazwyczaj to raczej posty wyświetla się po dacie.

Z drugiej strony to podzapytanie co tu przedstawiłem też działa szybko.

[wookieb]

Probleme jest wyliczanie pozycji dla dużych tematów, w dużym forum. Poza tym nigdy w życiu nie widziałem sensu innego kolejkowania postów niż ASC (desc jeszcze ma sens ale do tego celu również możesz wykorzystać POSITION).
Problemem jest również wyliczanie pozycji posta dla listy tematów (np wyszukiwanie) Wyliczenie pozycji razy liczba tematów na stronie = całkiem sporo zadań dla bazy. Szczerze mówiąc wolę raz przenumerować (bez problemu zaprzęgamy triggery) posty niż ciągłą zabawę z wyliczeniem pozycji.

[nospor]

Problemem jest również wyliczanie pozycji posta dla listy tematów (np wyszukiwanie) Wyliczenie pozycji razy liczba tematów na stronie = całkiem sporo zadań dla bazy

Ale kiedy ty chcesz te wyliczanie pozycji wykonywać? Dla każdego tematu na liście tematów? EEEE, a po co?

[wookieb]

Na liście wyszukiwania może zaistnieć potrzeba wyświetlenia którym postem w temacie jest znaleziony rekord (może ale nie musi). Po prostu jest to jedno z zagadnień optymalizacyjnych.
Szczerze mówiąc nie chce mi się szukać innych możliwych zastosowań ale tak jak wspomniałem. Lepiej wyliczyć raz i mieć spokój.

Ten post edytował wookieb 11.08.2010, 08:18:53

[nospor]

Na liście wyszukiwania może zaistnieć potrzeba wyświetlenia którym postem w temacie jest znaleziony rekord (może ale nie musi)

Ale po co?
Weźmy przykład z tego forum:
Na liscie postów (czy na liście wyszukiwania postów) mamy coś takiego jak link do posta:
.....&view=findpost&p=776621
W linku nie ma numeru strony. Na tym etapie nie pobieramy pozycji posta. Wchodząc na link, forum przelicza stronę na której znajduje się post (robi to zapewne na podstawie pozycji posta) i przekierowuje jeszcze raz na ten sam temat ale z uwzględnieniem strony.
No i tu pytanie czy pozycja jest juz zapisana w bazie i poprostu brana, czy też może jest wyliczana gdy ktoś kliknie na ten link.
Wysłałem to pytanie do naszego admina to może się dowiemy jak to jest tu na forum zrobione (IMG:style_emoticons/default/smile.gif)

edit: no i mam odpowiedź od kwiateusza:
w tabeli postów tu na forum nie ma takiego pola jak pozycja. Pozycja jest wyliczana na żądanie. No i jakoś to duże forum sobie z tym radzi.
Tak naprawdę trudno by sobie nie radziło. Weźmy np. to moje zapytanie:
już na dzien dobry ograniczam wyszukiwania tylko do postów należącyc do danego tematu (FK_TOPIC). Przy poprawnych indeksach takie zapytania są naprawdę szybkie.

Dzięki wookieb jednak za dyskusję. Nie powiem, przydała się by spojrzeć na to wszystko pod trochę innym kontem.

[wookieb]

Znowu się przyczepię (IMG:style_emoticons/default/smile.gif) Kolumna position jest lepsza przy paginacji (IMG:style_emoticons/default/smile.gif) Mogę to szerzej omówić ale jest trochę pisania więc tylko na życzenie (IMG:style_emoticons/default/smile.gif)

[nospor]

Kolumna position jest lepsza przy paginacji Mogę to szerzej omówić ale jest trochę pisania więc tylko na życzenie

Z miłą chęcią wysłucham (IMG:style_emoticons/default/smile.gif)

[wookieb]

Tabela

[SQL] pobierz, plaintext 
CREATE TABLE `paginacja_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `pole` varchar(100) NOT NULL,
 `id_pomocnicze` int(11) NOT NULL,
 `pozycja` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_pomocnicze_2` (`id_pomocnicze`,`pozycja`)
);
[SQL] pobierz, plaintext 

Wypełniamy przykładowymi danymi

[PHP] pobierz, plaintext 
$pdo->beginTransaction();
$max = 1000;
$id_pom = 1;
$pozycja = 1;
for ($i=0; $i<1000; $i++)
{
    $pdo->query('INSERT INTO paginacja_test (pole, id_pomocnicze, pozycja) VALUES ("'.md5(rand(0, 1000000)).'", '.$id_pom.', '.$pozycja.')');
    if ($i%100 == 0) {
        $id_pom++;
        $pozycja = 1;
    } else {
        $pozycja++;
    }
}
$pdo->commit();
[PHP] pobierz, plaintext 

[SQL] pobierz, plaintext 
EXPLAIN 
SELECT * 
FROM `paginacja_test`
WHERE id_pomocnicze =5 AND pozycja BETWEEN 60 AND 79
ORDER BY pozycja
[SQL] pobierz, plaintext 

Przeskanowanych rekordów 20

[SQL] pobierz, plaintext 
EXPLAIN
SELECT * FROM `paginacja_test`
WHERE id_pomocnicze =5
ORDER BY pozycja
LIMIT 20
OFFSET 60
[SQL] pobierz, plaintext 

Przeskanowanych rekordów 100

W zapytaniu z limit baza szuka wszystkich rekordów z id_pomocnicze = 5, sortuje a następnie odrzuca 60. Jeżeli do warunku dorzucimy dodatkowy warunek wyboru baza danych dzięki indeksowi nie musi znajdywać aż 100 rekordów.

Pozycja wygrywa (IMG:style_emoticons/default/smile.gif)

P.s. Wiem, że się czepiam ale cóż taki już jestem (IMG:style_emoticons/default/smile.gif)

[nospor]

Hehe, w takim sensie paginacja... (IMG:style_emoticons/default/smile.gif) No tak też można.

P.s. Wiem, że się czepiam ale cóż taki już jestem

Ani razu tu nie powiedziałem ze się czepiasz. Wręcz przeciwnie, miło poczytać (IMG:style_emoticons/default/smile.gif)

Ta metoda ma jedną potencjalną wadę: trzeba naprawdę mocno pilnować, by te pozycje były dobrze wyliczone i nie miały żadnych "dziur"

[wookieb]

Ta metoda ma jedną potencjalną wadę: trzeba naprawdę mocno pilnować, by te pozycje były dobrze wyliczone i nie miały żadnych "dziur"

Dlatego w takich miejscach trigger + transakcje wydaje się być nieocenionym skarbem.

[nospor]

trigger - nie każdy hosting na to pozwala. W zasadzie pracowałem na kilku i żaden na to nie pozwalał
transakcje - forum robię na myisam by szybciej działało. Tam nie ma transakcji (IMG:style_emoticons/default/smile.gif)