optymalizacja zapytań i bazy danych Opcje

[Arek00]

przeglądając phpmyadmin znalazłem coś takiego:


w jaki sposób zabrać się za usunięcie tych błędów? jak sprawdzić czy zapytanie poprawnie używa indeksów?

[kitol]

Używając explain:

[SQL] pobierz, plaintext 
EXPLAIN SELECT (dalsza część zapytania)
[SQL] pobierz, plaintext 

z analizy otrzymanych informacji można wywnioskować co jest nie tak. Generalnie wszystkie kolumny których używamy w JOIN powinny mieć założone indeksy.

[Aztech]

Przeczytaj sobie artykuły do których linkowałem w tym poście w szczególności ten z EIOBA.

[Arek00]

znalazłem takie zapytanie:

[SQL] pobierz, plaintext 
SELECT opis_nazwy.id_produktu, opis_nazwy.nazwa
FROM sklep_cennik LEFT JOIN opis_nazwy ON opis_nazwy.id_produktu = sklep_cennik.id_produktu
WHERE opis_nazwy.id_produktu IS NOT NULL AND sklep_cennik.dostepnosc LIKE 'T'
ORDER BY rand( )
LIMIT 9
[SQL] pobierz, plaintext 

wynik polecenia explain:

Kod

id|select_type|table       |type|possible_keys     |key    |key_len|ref                            |rows|Extra
1 |SIMPLE     |opis_nazwy  |ALL |PRIMARY           |NULL   |NULL   |NULL                           |4834|Using where; Using temporary; Using filesort
1 |SIMPLE     |sklep_cennik|ref |PRIMARY,dostepnosc|PRIMARY|194    |drupal47.opis_nazwy.id_produktu|1   |Using where

z tego co zdążyłem się dowiedzieć to przy tabeli opis_nazwy też powinien być wykorzystywany klucz. jak to poprawić?

[kitol]

W zapytaniu zmień:

[SQL] pobierz, plaintext 
sklep_cennik.dostepnosc LIKE 'T'
[SQL] pobierz, plaintext 

na

[SQL] pobierz, plaintext 
sklep_cennik.dostepnosc='T'
[SQL] pobierz, plaintext 

Wydaje mi się że:

[SQL] pobierz, plaintext 
LEFT JOIN opis_nazwy ON opis_nazwy.id_produktu = sklep_cennik.id_produktu
WHERE opis_nazwy.id_produktu IS NOT NULL
[SQL] pobierz, plaintext 

lepiej zamienić na:

[SQL] pobierz, plaintext 
RIGHT JOIN opis_nazwy ON opis_nazwy.id_produktu = sklep_cennik.id_produktu
[SQL] pobierz, plaintext 

Proponuję takie zapytanie:

[SQL] pobierz, plaintext 
SELECT opis_nazwy.id_produktu, opis_nazwy.nazwa
FROM sklep_cennik RIGHT JOIN opis_nazwy ON opis_nazwy.id_produktu = sklep_cennik.id_produktu
WHERE sklep_cennik.dostepnosc='T'
ORDER BY rand()
LIMIT 9
[SQL] pobierz, plaintext 

Widzę że tabele są łączone po PRIMARY KEY więc jest OK. Czy id_produktu to INT, czy coś innego?
Poza tym indeks powinien być założony na sklep_cennik.dostepnosc.

[Arek00]

przy takim zapytaniu:

[SQL] pobierz, plaintext 
SELECT opis_nazwy.id_produktu, opis_nazwy.nazwa
FROM sklep_cennik RIGHT JOIN opis_nazwy ON opis_nazwy.id_produktu = sklep_cennik.id_produktu
WHERE sklep_cennik.dostepnosc = 'T'
ORDER BY rand( )
LIMIT 9
[SQL] pobierz, plaintext 

wygląda to tak:

Kod

id|select_type|table       |type|possible_keys     |key       |key_len|ref                              |rows|Extra
1 |SIMPLE     |sklep_cennik|ref |PRIMARY,dostepnosc|dostepnosc|8      |const                            |3655|Using where; Using temporary; Using filesort
1 |SIMPLE     |opis_nazwy  |ref |PRIMARY           |PRIMARY   |44     |drupal47.sklep_cennik.id_produktu|1   |Using where

na sklep_cennik.dostepnosc byl zalozony klucz juz wczesniej

nie orientuję się dobrze o co chodzi z tymi kluczami. id_produktu to varchar(20) i jest jako primary.

[kitol]

Nowe zapytanie na EXPLAIN wygląda lepiej. Possible keys - są to indeksy z których baza może skorzystać. W kolumnie "key" są indeksy które faktycznie wykorzystuje. W drugim przypadku w obu tabelach używa indeksów - "dostepnosc" dla tabeli 'sklep_cennik' oraz PRIMARY dla tabeli opis_nazwy. Powinieneś sprawdzić czy zmniejszył się czas wykonywania zapytania - phpMyAdmin i podobne podają ten czas. Wykonaj zapytania w obu wersjach i porównaj. Sprawdź też czy wyniki obu zapytań są prawidłowe. Identyczne nie będą bo wybierasz rekordy losowe. Funkcja LIKE której wcześniej użyłeś wyklucza sprawdzanie po indeksie dlatego równoznaczne ='T' jest lepsze.  

[Arek00]

to co podawałem było z mojego komputera. teraz próbuję sprawdzić to samo zapytanie na serwerze zdalnym:

[SQL] pobierz, plaintext 
SELECT opis_nazwy.id_produktu, opis_nazwy.nazwa
FROM sklep_cennik RIGHT JOIN opis_nazwy ON opis_nazwy.id_produktu = sklep_cennik.id_produktu
WHERE sklep_cennik.dostepnosc = 'T'
ORDER BY rand( )
LIMIT 9
[SQL] pobierz, plaintext 

Kod

id|select_type|table       |type|possible_keys     |key    |key_len|ref                            |rows|Extra
1 |SIMPLE     |opis_nazwy  |ALL |PRIMARY           |NULL   |NULL   |NULL                           |2849|Using temporary; Using filesort
1 |SIMPLE     |sklep_cennik|ref |PRIMARY,dostepnosc|PRIMARY|194    |drupal47.opis_nazwy.id_produktu|1   |Using where

nie wiem dlaczego ale działa inaczej...

[kitol]

Może jest to kwestia różnicy wersji mySql'a na obu serwerach?? Porównaj również definicję tabeli (typy danych i indeksy)

[Arek00]

na komputerze mam mysql 5.0.27 a na serwerze zdalnym 5.0.26. struktura i indeksy są identyczne więc to zapytanie powinno być wykonywane tak samo tu i tu.