[MySql] Nie szuka po kolumnach indeksowanych. Opcje

[mariuszg]

witam,

MySql Query Browser podczas:

[SQL] pobierz, plaintext 
EXPLAIN SELECT marka,model,typ id_dane,stan, rocznik,cena,region,przebieg, data_aktywacji, kategoria, typ_oferty, osobowe_marka.id, rocznik, rocznik  FROM ogloszenia_02.dane LEFT JOIN ogloszenia_02.osobowe_marka ON osobowe_marka.id = dane.id_marka
      LEFT JOIN ogloszenia_02.osobowe_model ON osobowe_model.id = dane.id_model
      LEFT JOIN ogloszenia_02.osobowe_typ ON osobowe_typ.id   = dane.id_typ
 
       WHERE rocznik > 2005 AND rocznik < 2009 AND stan = 1 AND kategoria = 1 AND typ_oferty = 1
 
       ORDER BY cena  DESC
[SQL] pobierz, plaintext 

pokazuje że:

possible_keys - stan,kategoria,typ_oferty,rocznik
key - stan


czas wyszukiwania około 2,5 s

czasami szuka po indeksie rocznik i wtedy wyszukiwanie trwa 0,02 s

Dlaczego zawsze nie wyszukuje po roczniku ?

tabela:

[SQL] pobierz, plaintext 
CREATE TABLE `dane` (
  `id_dane` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_uzytkownik` int(10) UNSIGNED NOT NULL,
  `fp` tinyint(1) UNSIGNED DEFAULT NULL,
  `kategoria` tinyint(1) UNSIGNED DEFAULT NULL,
  `typ_oferty` tinyint(1) UNSIGNED DEFAULT NULL,
  `data_dodania` datetime DEFAULT NULL,
  `data_aktywacji` datetime DEFAULT NULL,
  `stan` tinyint(1) UNSIGNED DEFAULT NULL,
  `wyroznienie` tinyint(1) UNSIGNED DEFAULT NULL,
  `fotografie` tinyint(1) UNSIGNED DEFAULT NULL,
  `id_marka` int(10) UNSIGNED DEFAULT NULL,
  `id_model` int(10) UNSIGNED DEFAULT NULL,
  `id_typ` int(10) UNSIGNED DEFAULT NULL,
  `rocznik` smallint(11) UNSIGNED DEFAULT NULL,
  `miesiac` varchar(3) DEFAULT NULL,
  `przebieg` int(10) UNSIGNED DEFAULT NULL,
  `cena` int(10) UNSIGNED DEFAULT NULL,
  `region` char(2) DEFAULT NULL,
  `typ_silnika` char(10) DEFAULT NULL,
  `silnik` varchar(2) DEFAULT NULL,
  `pojemnosc` char(5) DEFAULT NULL,
  `moc` char(5) DEFAULT NULL,
  `typ_nadwozia` char(2) DEFAULT NULL,
  `drzwi` char(2) DEFAULT NULL,
  `kolor` varchar(20) DEFAULT NULL,
  `o_1` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_2` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_3` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_4` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_5` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_6` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_7` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_8` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_9` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_10` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_11` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_12` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_13` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_14` tinyint(1) UNSIGNED DEFAULT NULL,
  `o_15` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_1` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_2` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_3` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_4` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_5` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_6` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_7` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_8` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_9` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_10` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_11` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_12` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_13` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_14` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_15` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_16` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_17` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_18` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_19` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_20` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_21` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_22` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_23` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_24` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_25` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_26` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_27` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_28` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_29` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_30` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_31` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_32` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_33` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_34` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_35` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_36` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_37` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_38` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_39` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_40` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_41` tinyint(1) UNSIGNED DEFAULT NULL,
  `w_42` tinyint(1) UNSIGNED DEFAULT NULL,
  `sciezka` varchar(50) DEFAULT NULL,
  `foto_a` varchar(50) DEFAULT NULL,
  `foto_b` varchar(50) DEFAULT NULL,
  `foto_c` varchar(50) DEFAULT NULL,
  `foto_d` varchar(50) DEFAULT NULL,
  `foto_e` varchar(50) DEFAULT NULL,
  `foto_f` varchar(50) DEFAULT NULL,
  `foto_g` varchar(50) DEFAULT NULL,
  `foto_h` varchar(50) DEFAULT NULL,
  `foto_i` varchar(50) DEFAULT NULL,
  PRIMARY KEY  (`id_dane`),
  KEY `FK_id_marka` (`id_marka`),
  KEY `stan` (`stan`),
  KEY `kategoria` (`kategoria`),
  KEY `typ_oferty` (`typ_oferty`),
  KEY `data_aktywacji` (`data_aktywacji`),
  KEY `rocznik` (`rocznik`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=100006 ;
[SQL] pobierz, plaintext 

[nevt]

co masz na myśli CZASAMI TO SAMO zapytanie raz się wykonuje 0,02 sek. a innym razem 2,5 sek czy to są DWA RÓŻNE ZAPYTANIA

pokaż je (warianty zapytań) - wtedy coś można wnioskować...

a przy okazji - pola:
o_1 - o_15,
w_1 - w_42,
foto_a - foto_i

bez względu na przeznaczenie są złym pomysłem i powinny wylądować w oddzielnych tabelach

[mariusz g]

o_1 - o_15, - tylko 0 lub 1 - np jeśli ma ABS to 1, czyli lepiej w osobnej tabeli
w_1 - w_42, - taki jak pierwszy tylko inne opcje
foto_a - foto_i - nazwa pliku fotek


jeśli zapytanie jest takie

[SQL] pobierz, plaintext 
WHERE rocznik > 2005 AND rocznik < 2009
[SQL] pobierz, plaintext 

to używa indeksu rocznik

jak to zrobić żeby używał odpowiednich indeksów ?

[gino]

Jak to stwierdzasz, że czasem wyszukuje po indexie a czasem nie Czas wykonania zapytania jest uzależniony od wielu czynników nie tylko od indexów, ale także obciążenia bazy w momencie zapytania czy wydajności łącza. Jeśli index po kolumnie którą szukasz jest zdefiniowany to wyszukuje po indexie. Im bardziej założone zapytanie, duża ilość rekordów oraz w nielogicznej kolejności skonstruowane tym odpowiedz bazy trwa dłużej. Sortowanie odbywa sie po kolei jak w zapytaniu, ważne jest więc dokładne zaprojektowanie bazy danych.
gino

[mariusz g]

explain mi pokazuje

possible_keys - stan,kategoria,typ_oferty,rocznik -- po jakich może
key - stan - po jakich szuka

[gino]

ok, spotkałem kiedyś osąd, że MySQL nie zawsze wybiera najlepszą możliwość w danym zapytaniu w kwestii użycia indexów. Taki urok bazy.

gino


edit:

posible_keys i key pozwala nam dowiedzieć się z jakich innych indeksów możemy próbować korzystać a jak zrobi MySQL to już inna sprawa, j.w.

Ten post edytował gino 26.02.2008, 18:32:31

[nevt]

jeżeli masz dużo zapytań z użyciem kilku pól ( u ciebie: rocznik, typ_oferty, kategoria, stan) a nie potrzebujesz super wydajności przy INSERT i UPDATE to rozważyłbym założenie dodatkowego indeksu wielopolowego (na te wszystkie pola po których szukasz) - to powinno ożywić twoje SELECT...

[mariusz g]

Czy mogę zrobić indeks wielopolowy zawierający np 15 pozycji, bo po tych pozycja będzie wyszukiwanie, czasami po dwóch a czasami po 3,4,5,6,7,8 itd.

użytkownik będzie miał formularz na stronie i on wybiera to co potrzebuje, więc ja to objąć w sensowną całość ?
jeśli zawsze w wyszukiwani były by 4 pola to ok, ale to się zmienia

np:

marka,model,typ id_dane,stan, rocznik,cena,region,przebieg, data_aktywacji, kategoria, typ_oferty, osobowe_marka.id, rocznik, rocznik

czy to nie pogorszy wyszukiwania ?

[nevt]

na pewno powiększy mocno bazę, na pewno spowolni insert i update - a czy przyspieszy select ? spróbuj - załóż indeks, przetestuj wydajność - my nie mamy tej bazy ani tego formularza - jak mamy ci to sprawdzić...

wg mnie problem może być głębszy - niewydajna struktura bazy przy dużej ilości danych i skomplikowanych zapytaniach... wtedy żadne indeksy nie pomogą jeżeli nie zmienisz schematu bazy...

Ten post edytował nevt 28.02.2008, 21:06:26