[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