Potrzebuję zoptymalizować zapytania MySQL na swojej stronie. Od pewnego czasu, jako że popularność strony mocno wzrosła, serwer już nie daje rady i prawie codziennie przychodzi mi na maila powiadomienie o przekroczeniu limitu obciążenia MySQL - ponad 1800s/osobę. Administrator serwera poprosił o optymalizację skryptów. W MySQL-u tak mocno nie siedzę i nie za bardzo wiem jakie są dobre praktyki, które pomogą zoptymalizować działanie skryptów.

To co do tej pory udało mi się zrobić to:
- Nieco ograniczyć ilość zapytań
- Zmienić mysql_fetch_array na assoc (nie wiem czy to pomoże w optymalizacji)
- Zmienić mysql_num_rows na zapytanie z SELECT COUNT(*)

Co jeszcze mogę zrobić żeby odciążyć nieco MySQL-a? Linki do stron, artykułów o tej tematyce mile widziane. Sam nie znalazłem, tego czego szukałem.

Poczytaj o INDEX'ach w Mysql oraz zacznij korzystać z EXPLAIN W zapytaniach SQL

co do pierwszego popieram w pełni - co do drugiego nie do końca. Jeśli wykonujesz jakiś SELECT i używasz danych z niego pobranych to lepiej jest użyć mysql_num_rows niż stosować drugie zapytanie. Pomyśl o tym.

Zlinczują mnie za to ale masz tu link do mojego wpisu - to instrukcje w pigułce co do MySQL - nie da Ci to wszystkich odpowiedzi ale będziesz miał punkt zaczepienia

HTH

1. INDEXY
2. stronicowanie (paginacja) danych
3. nie używanie *
4. mysql_slow.log

- używaj PDO
- do zliczania:

[PHP] pobierz, plaintext 
$sql = "SELECT count(id) FROM `table` WHERE foo = bar"; 
$result = $con->prepare($sql); 
$result->execute(); 
$number_of_rows = $result->fetchColumn();
[PHP] pobierz, plaintext 

- nie używaj *

1. PDO jest szybsze? Mogę użyć zwyczajnie "SELECT COUNT(id) as SUMA FROM table WHERE pole='jakieś tam'"
2. Jeśli chodzi o INDEXy to stosuję PRIMARY przy ID. Potem w wolnej chwili doczytam o reszcie INDEXów.
3. O co chodzi ze paginacją danych? Paginację to ja stosuję ale przy listowaniu np. wpisów Chodzi mi o np. LIMIT 5,10
4. Jeśli chodzi o mysql_slow.log to niestety nie mam do niego dostępu (zwyczajny hosting). Chyba, że jest jakiś sposób na wyciągnięcie tego pliku? (administrator niestety nie chce przesłać)

Jeszcze mam jedno pytanie. Lepszym (szybszym?) rozwiązaniem jest:

[PHP] pobierz, plaintext 
mysql_query("SELECT * FROM tabela WHERE pole='$zmienna' ");
[PHP] pobierz, plaintext 

czy

[PHP] pobierz, plaintext 
mysql_query("SELECT * FROM tabela WHERE pole='".$zmienna."' ");
[PHP] pobierz, plaintext 

Ogólnie, to zależy - jeśli korzystasz z prepared statements, to w przypadku np. insertów w pętli różnica jest spora, bo można wykorzystać mapowanie pamięci.


I dziwisz się, że wolno chodzi?


Postaw sobie lokalną kopię i użyj EXPLAIN wobec wszystkich zapytań.


To jest kosmetyczna optymalizacja, na poziomie parsera.

erix, co postawić przy ID? PRIMARY według mnie () wydaje się sensowy, a i czas wykonywania go przez MySQL jest krótki.

Poczytałem o INDEX'ach i co nieco wiem. Większośc materiałów jednak nie tłumaczy do końca jaki index gdzie i jak stosować (albo po prostu ja jestem taki tępy)...

Powiedzmy, że na stronie wyświetlam np. 20 rekordów które sortuje po polu POZYCJA. Wtedy do pola POZYCJA dodaję INDEX, tak? Użyłem EXPLAIN i rzeczywiście pokazuje, że po nadaniu INDEX'u szuka tylko w 20 rekordach a nie w ponad 4000, które mam. Tak więc przy nadawaniu INDEX'ów kierować się po prostu czasem wykonywania zapytania i liczbą przeszukiwanych rekordów? Hmm to wydaje się proste

EDIT: Coś dziwnego mi się stało. Na lokalnym serwerze po dodaniu INDEX'u w pewnym zapytaniu, w EXPLAIN wyskakuje mi ROWS=3 - czyli dobrze. Natomiast na właściwym serwerze dalej ROWS=600. Mimo, że jest to dokładna kopia bazy.

Miałem na myśli drugą część zdania.

http://schimpf.es/mysql-explain-tutorial/

Ahhh ok No, można powiedzieć że dodałem indeksy i szybkość zapytań znacznie wzrosła w niektórych przypadkach. Czytałem, że INDEX'y jednak mogą zajmować dużo miejsca na dysku i nie warto tworzyć ich nie wiadomo ile. Czy oprócz zajmowania dysku robią coś nie dobrego? Miejsca na serwerze jeszcze mam więc w sumie ich waga mi wisi póki co Jak na razie, najwięcej ważące INDEX'y mają w jednej tabeli 600KB, ale to pewnie dlatego że mam tam dwa FULLTEXT'y.

Z tego, co wiem, to nie. Po prostu leżą na dysku i ew. DBMS sprawdza, czy może użyć danego indeksu na pola.

Wagą bym się nie przejmował.

A jeśli FULLTEXT okazałby się za wolny - polecam coś w rodzaju Sphinksa.

Oprócz zajmowania miejsca, trzeba je jeszcze przy zapisie danych zaktualizować, przeliczyć, dlatego należy szukać złotego środka i nie dodawać takich, z których się nie korzysta albo korzysta się bardzo rzadko.

Yup, racja, skleroza nie boli. [;