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?


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

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

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?


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.

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

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.

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.
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.

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/