SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 19.08.2010 13:52:39

savit
Завсегдатай
Зарегистрирован: 10.11.2009
Сообщений: 25

Не используется индекс

Приветствую!

Собственно запрос примерно такого вида (вариация параметров where в зависимости от того что задал юзер) :


SELECT a.id
FROM ads a
INNER JOIN geo g ON a.geo_id = g.geo_id
INNER JOIN metro m ON a.metro_id = m.metro_id
INNER JOIN cat c ON a.cat_id = c.cat_id
INNER JOIN params_realty_apartments p ON a.id = p.id
WHERE c.cat_lft >=4 AND c.cat_rgt <=9
AND ( g.geo_lft >=6 AND g.geo_rgt <=7 )
OR ( g.geo_lft >=8 AND g.geo_rgt <=9 )
OR (g.geo_lft >=10 AND g.geo_rgt <=11 )
OR ( g.geo_lft >=12 AND g.geo_rgt <=13 )
OR ( g.geo_lft >=14 AND g.geo_rgt <=15 )
OR ( m.metro_id =76 )
OR (m.metro_id =98 )
OR (m.metro_id =146 )
OR (m.metro_id =169 )
AND p.total_rooms =1
AND a.price >=2
AND a.price <=5000000
AND a.total_area >=1
AND a.total_area <=100
AND a.vip >1282208163
AND a.status =1
 


EXPLAIN

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     p     ALL     id     NULL     NULL     NULL     2005      
1     SIMPLE     a     eq_ref     PRIMARY,cat_id,geo_id     PRIMARY     3     nedv.p.id     1      
1     SIMPLE     m     eq_ref     PRIMARY     PRIMARY     2             nedv.a.metro_id     1     Using index
1     SIMPLE     c     eq_ref     PRIMARY,cat_lft     PRIMARY     2     nedv.a.cat_id     1      
1     SIMPLE     g     eq_ref     PRIMARY,geo_lft     PRIMARY     2     nedv.a.geo_id     1     Using where
 


Итого для поиска 20-30 vip-объектов по заданным параметрам идет перебор 2005 строк в следствии не использования индекса ... не кошерно получается ;(
Буду благодарен за дельные советы по добавлению/изменению индексов ... в общем хотелось бы уменьшить количество перебираемых строк.


Урезанная структура таблиц (удалены некоторые столбцы не участвующие в запросе):


CREATE TABLE IF NOT EXISTS `ads` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `dt` int(10) unsigned DEFAULT '0',
  `total_area` float unsigned NOT NULL DEFAULT '0',
  `price` decimal(11,2) unsigned DEFAULT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `vip` int(10) unsigned DEFAULT '0',
  `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `geo_id` smallint(5) unsigned DEFAULT '0',
  `metro_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `cat_id` (`cat_id`),
  KEY `geo_id` (`geo_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `geo` (
  `geo_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `geo_name` char(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  `geo_lft` smallint(5) unsigned DEFAULT '0',
  `geo_rgt` smallint(5) unsigned DEFAULT '0',
  `geo_level` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY (`geo_id`),
  UNIQUE KEY `geo_lft` (`geo_lft`,`geo_rgt`,`geo_level`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `cat` (
  `cat_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `cat_name` char(55) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cat_lft` smallint(5) unsigned DEFAULT NULL,
  `cat_rgt` smallint(5) unsigned DEFAULT NULL,
  `cat_level` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`cat_id`),
  UNIQUE KEY `cat_lft` (`cat_lft`,`cat_rgt`,`cat_level`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `params_realty_apartments` (
  `id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `total_rooms` tinyint(3) unsigned NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


 

Неактивен

 

#2 19.08.2010 14:03:26

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Не используется индекс

Нужно сделать нормальные индексы на часто встречающихся в WHERE
полях (например, метро, улица). Ну и переписать запрос так, чтобы
были =, а не >=, т.к. в Вашем случае следующее эквивалентно:
( g.geo_lft >=12 AND g.geo_rgt <=13 )
и
geo_lft IN (12, 13).

Неактивен

 

#3 19.08.2010 14:25:08

savit
Завсегдатай
Зарегистрирован: 10.11.2009
Сообщений: 25

Re: Не используется индекс

paulus, спасибо за комментарий

в моем случае ( g.geo_lft >=12 AND g.geo_rgt <=13 ) не эквивалентно geo_lft IN (12, 13) т.к тут два разных столбца (geo_lft и geo_rgt ) ... тамблица geo это дерево nested sets и поэтому нужно именно >= и <= ( чтобы учитывались дочерние элементы )

Неактивен

 

#4 19.08.2010 15:18:07

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Не используется индекс

Ах вот оно что, прочитал неудачно smile

Тогда и правда не эквивалентны. Только мне всегда казалось, что в схемах
с левым-правым полями все поля попадают в родительский диапазон. Т.е.
при правильной организации данных верно, что из geo_lft BETWEEN 12 AND 13
следует geo_rgt BETWEEN 12 AND 13 при условии, что 12 и 13 — это left и right
родительского элемента. Тогда можно выбросить половину условий (а соседние
интервалы объединить — в Вашем примере это будет вообще один интервал).

Неактивен

 

#5 19.08.2010 15:30:47

savit
Завсегдатай
Зарегистрирован: 10.11.2009
Сообщений: 25

Re: Не используется индекс

Согласен, от правых значений действительно можно избавится вовсе и оставить везде geo_lft BETWEEN левое AND правое
И за идею объединения соседних интервалов тоже спасибо, над этим тоже поколдую ( но так канечно же бывает далеко не всегда ). Сейчас переделаю и посмотрю что получится!

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson