Witam,
Zaobserwowałem dziwne zjawisko. Może dotyczy ono SQL a być może bazy danych PostgreSQL 8.3 jakiej używam. Otóż zastosowanie LIMIT/OFFSET powoduje, że rezultany przy jednym offsecie częściowo pokrywają się z rezultatami przy innym offsecie. Oto przykład:
SELECT d.document_id FROM document d JOIN category c ON (d.category_id=c.category_id) JOIN page p ON (d.document_id=p.document_id AND p.order_sequence=1) WHERE ORDER BY d.weight, d.title
Zwraca mi 23 wyniki: 315;5;"Płatek" 314;5;"Płatek" 313;5;"Płatek" 303;5;"test" 304;5;"test2" 290;5;"Zapięcie" 291;5;"Zapięcie" 292;5;"Zapięcie" 293;5;"Zapięcie" 294;5;"Zapięcie" 295;5;"Zapięcie" 297;5;"Zapięcie" 298;5;"Zapięcie" 299;5;"Zapięcie" 300;5;"Zapięcie" 301;5;"Zapięcie" 302;5;"Zapięcie" 296;5;"Zapięcie" 286;5;"Zapięcie" 287;5;"Zapięcie" 288;5;"Zapięcie" 289;5;"Zapięcie" 285;5;"Zapięcie "
Każdy rekord występuje raz. Tymczasem zastosowanie tego samego zapytania lecz po uzupełnieniu go: LIMIT 10 OFFSET 0 daje: 315;5;"Płatek" 314;5;"Płatek" 313;5;"Płatek" 303;5;"test" 304;5;"test2" 302;5;"Zapięcie" 301;5;"Zapięcie" 300;5;"Zapięcie" 299;5;"Zapięcie" 298;5;"Zapięcie"
zupełnie inny porządek! Tylko 5 pierwszych rekordów jest w tym samym porządku co wcześniej. Mało tego, zastosowanie LIMIT 10 OFFSET 10 daje: 295;5;"Zapięcie" 297;5;"Zapięcie" 298;5;"Zapięcie" 299;5;"Zapięcie" 300;5;"Zapięcie" 301;5;"Zapięcie" 302;5;"Zapięcie" 296;5;"Zapięcie" 286;5;"Zapięcie" 287;5;"Zapięcie"
A tu się powtarzają rekordy 300 i 299 względem powyższego. O co tu może chodzić? Wiem, że kluczowe jest to, że dokumenty mają powtarzające się tytuły jednakże nie powinno to powodować, że rekordy z jednej grupy przechodzą do innej Jak temu zaradzić inaczej niż dopisując w ORDER BY kolejne kryterium d.document_id? Dopisanie tego typu argumentu znacznie obniża wydajność sortowania.
Powtarzające się rezultaty wyszukiwania.
Po co pokazujesz nam coś, po czym nie sortujesz? Zwraca mi 23 wyniki: 315;5;"Płatek"
Do tego to nie wygląda na dokument_id, zupełnie nie wiadomo, jak się ma do zapytania.
Ups, powinno być:
SELECT d.document_id, d.weight, d.title FROM document d JOIN category c ON (d.category_id=c.category_id) JOIN page p ON (d.document_id=p.document_id AND p.order_sequence=1) WHERE ORDER BY d.weight, d.title
Za dużo wyciąłem z zapytania. W rzeczywistości zwraca ono dużo więcej. Ograniczyłem ilość danych w celu zwiększenia czytelności problemu. Joiny też mają wpływ na kolejność wyników.
Musisz określić ścisłe kryteria sortowania, czyli dodać to d.document_id.
Zauważyłem, że wydajność indeksy nie przyspieszają zapytania, które ma 3 lub więcej kryteriów sortowania i dlatego unikałem takich konstrukcji. Z jednej strony nie zależy mi na kolejności zwracania dokumentów o tym samym tytule a z drugiej zaskakuje mnie to, że OFFSET powoduje zmianę kolejności gdy kryteria nie są ścisłe.
Jeśli chcesz zwiększyć czytelność, to zastanów się, co na, będzię potrzebne do analizy! Jeśli pytasz dlaczego sortowanie źle działa, to istotne są wartości kolumn sortowanych. Inne są nieistotne, bo nie mają wpływu na sortowoanie.
Masz listing pracy trzech wariantów zapytania: jedno bez limitów i dwa z zadanym offsetem. Pytanie SQL bez limitów chyba listuje wszystkie wartości kolumn jakie są dostępne? Dwa pozostałe listingi obrazują zjawisko powtarzania się w nich tych samych rekordów. Zwracane wartości są takimi jakie występują w order by plus kolumna z ID dokumentu. Nie rozumiem co więcej mógłbyś chcieć wiedzieć?