[CMS] Przypinanie newsów na szczyt, wydajny sposób, aby to osiągnąć Opcje

[WebCM]

Dużo serwisów wyświetla wybraną nowość na pierwszym miejscu, aby była cały czas widoczna i nie uciekła na inną stronę. Zazwyczaj są one wyróżnione. Czy taka funkcja w CMS-ach jest szczególnie przydatna?

Sposób 1. Opcja "Przypnij"
Dodajemy dodatkowe pole `pin` do tabeli `news`. Redaktor zaznacza opcję Przypnij newsa i już jest na samej górze. Wada tego rozwiązania: większa złożoność obliczeniowa mimo niekorzystania z tej funkcji:

[SQL] pobierz, plaintext 
SELECT * FROM news ORDER BY pin DESC, ID DESC
[SQL] pobierz, plaintext 

Indeks jest tylko na polu ID. Jak widać, trzeba dodatkowo sortować po polu `pin` bez indeksu. To musi trwać dłużej. Pole `pin` przyjmuje wartości 0 lub 1, chyba że dopuścimy większe.

Sposób 2. Data modyfikacji, data zdjęcia
Dodatkowe pole `expiry_time` pozwala wyświetlać news na szczycie do pewnego momentu. Podobnie jak wyżej, rozszerzamy komendę ORDER BY. Wady podobne, a zaleta - news zdejmie się automatycznie.

Sposób 3. Inny sposób?
Jak to zrobić, aby jak najmniej obciążać bazę danych i nie spowalniać generowania strony? Dodatkowo trzeba te newsy wyróżnić - prawdopodobnie po stronie PHP, a w szablonach może być tak:

A może wprowadzić taką funkcję tylko dla stron głównych, które byłyby cachowane?

Kod

<!-- START wyroznione -->
Tu wyróżnione newsy
<!-- STOP -->

<!-- START newsy -->
Tu zwykłe newsy
<!-- STOP -->

To można zastosować też do artykułów, plików i wszystkich typów zawartości.

[CuteOne]

1. Dlaczego pin nie miał by mieć indeksu?? mysql oferuje indeksy dla jednej, wielu lub wszystkich kolumn
2. Minus to +1 zapytanie
3. Cache wyróżnionych z opcją expire to chyba to czego szukasz

Ten post edytował CuteOne 13.08.2011, 17:08:16

[WebCM]

1. Jaki sens dawać indeks polu z wartościami 0 i 1? MySQL nawet z niego nie skorzysta.
2. Dodatkowe pole w ORDER BY nie powinno być zabójcze, ale im szybciej, tym lepiej.
3. Cache to dobre wyjście, ale sam cache nie zastąpi implementacji tego wszystkiego.

Zróbmy przykład:

[SQL] pobierz, plaintext 
SELECT * FROM newsy n LEFT JOIN uzytkownicy u ON n.autor = u.ID
WHERE n.kategoria = 1 AND n.opublikowany = 1
ORDER BY n.pin DESC, n.ID DESC
LIMIT 0,10
[SQL] pobierz, plaintext 

Ile dodatkowe pole pin w komendzie ORDER BY spowolni wykonanie zapytania przy dużej i małej ilości danych?

In some cases, MySQL can use an index to satisfy an ORDER BY clause without doing any extra sorting.

Podobny przykład: http://dev.mysql.com/doc/refman/5.0/en/ord...timization.html

MySQL może użyć klucza ID do komendy WHERE, ale sortowanie już odbywa się bez indeksów.

Dodatkowe pole + więcej sortowania - to musi trwać dłużej. Zobaczę, co powie EXPLAIN.

[CuteOne]

Cache nie ma zastąpić tylko wspomóc. Jeżeli wyróżnieni nie zmieniają się dynamicznie co wejście na stronę cachowanie ich zaoszczędzi ci sporo zasobów bazy

[deha21]

Odpowiadając na pytanie czy taka funkcja jest potrzebna w CMSach, odpowiem: bardzo. Sam używam. Ale ja to rozwiązałem inaczej ;p
Po prostu zrobiłem miejsce gdzie wyświetla się główny news -> w bazie ma np. pole 'glowny' , gdzie 1 to główny a 0 to zwykły. Jeśli jest 1 to go wyświetla, jeśli nie, to nie. Nie wiem jak to wpływa na bazę ale to chyba proste rozwiązanie? Twoje pierwsze rozwiązanie też fajne.

[WebCM]

Co wyświetla polecenie EXPLAIN?

Gdy sortujemy tylko po kluczu podstawowym ID DESC - using where
Gdy sortujemy dodatkowo po polu pin bez indeksu pin DESC, ID DESC - using where, using filesort
Gdy sortujemy dodatkowo po polu pin z indeksem pin DESC, ID DESC - using where, using filesort

Nie da się posortować newsów, korzystając wyłącznie z indeksów. Trzeba przelecieć całą tabelę. Gdy jest 1000 newsów w 1 kategorii, MySQL uruchamia algorytm quicksort dla 1000 rekordów - tylko po to, aby wyróżnić 1 lub kilka newsów!

Eksperyment z datą

Nałożyłem zwykły indeks na pole date i sortuję po dacie i ID. Nie obędzie się bez algorytmu filesort. Sortuję tylko po dacie - to samo. Dlatego, że w ORDER BY odwołujemy się do innego indeksu niż w instrukcji WHERE.

Kiedy MySQL nie użyje indeksów?

Czytajcie tu: Optymalizacja ORDER BY - kiedy MySQL nie użyje indeksów i algorytm filesort

Mam jeszcze inny pomysł (być może) stosowany w wielu serwisach. Załóżmy, że wyświetlamy 10 newsów na stronie. Baza sortuje tylko po ID lub dacie. Wyróżnionego newsa szukamy po stronie PHP.

Kod

if($news['pin'])
{
  $wyroznione[] = array( /* dane z bazy */ );
}
else
{
  $zwykle[] = array( /* dane z bazy */ );
}

Jeżeli news spadnie na kolejną stronę, nie będzie już wyświetlany na szczycie pierwszej strony!

Które rozwiązanie jest najlepsze?
1) Dodatkowe pole `pin` do przypinania newsów i sortowanie po polu pin i ID
2) Możliwość zmiany daty i sortowanie po dacie
3) Dodatkowe pole `pin`, ale nie sortujemy po nim (przypięte newsy spadną na kolejne strony)
4) Rozwiązać to inaczej - jak?
5) Zbędna funkcja niepotrzebnie obciążająca bazę
6) Dać wybór, jak będą sortowane newsy

Test wykonałem tylko na 3 rekordach. Jutro wstawię losowe wartości i przetestuję na 1000.

Ten post edytował WebCM 19.08.2011, 00:14:43

[Noidea]

Tak jak wyżej wspomniałeś mysql nie korzysta z indeksów nałożonych na kolumnach ze zbyt małym zróżnicowaniem danych, bo mu się to nie opłaca. Z tym, że w przypadku 1000 tematów, z czego 2 są przypięte, indeks znalazłby zastosowanie. Powody mogą być dwa:
- Optymalizator widzi coś, czego ty nie widzisz
- Optymalizator jest zbyt głupi i trzeba mu podpowiedzieć, żeby korzystał z indeksu (LINK)

Ja obstawiam pierwszą wersję. Indeks pin to drzewo o 2 gałęziach, a w każdej z nich jest lista z adresami wierszy. Jako że indeksy działają tylko w jedną stronę (na podstawie wartości indeksu da się odnaleźć adres wiersza, ale na podstawie adresu nie da się odnaleźć wartości), to indeks ID staje się bezużyteczny. Więc masz 2 grupy adresów wierszy, które trzeba odczytać, wyciągnąć z nich pole ID i posortować - także zysku w wydajności nad zwykłym filesortem nie ma. I tak tez twierdzi optymalizator mysql.
Zamiast indeksu na kolumnie `pin`, utwórz indeks na parze kolumn: `pin`, `ID`

PS. Ilość wierszy tabeli też ma wpływ na decyzje optymalizatora, wiec testuj to na więcej niż 3 wierszach
PS2. Dodatkowa lektura:
http://stackoverflow.com/questions/231125/...d-in-sql-server
http://webmonkeyuk.wordpress.com/2010/09/2...-2-cardinality/

Ten post edytował Noidea 19.08.2011, 08:56:31