Оптимизация запроса

dmsoh · 26 Апр 2009

Есть сайт с объявлениями, больше 5000. На каждой страничке списка объявлений (1,2,3...) примерно 50 запросов к базе данных MySql. Время генерации страницы очень большое до 14 секунд + время загрузки примерно 3 сек, итого сайт очень тормозит, хотя работает на хорошем VPS.

При этом, если 49 запросов занимают доли секунд, то один - 70 % всего времени генерации - запрос на выборку списка объявлений. Почему так происходит я не знаю, ведь выборка ограничивается 10-ю результатами.

Вот сам запрос:

Код:

(11.4073410034 sec)

SELECT a.*, a.mileage*(1 + a.mileage_unit*0.609) as metric_mileage, a.mileage*(1 - (1 - a.mileage_unit)*0.3785) as english_mileage,
 p.id as photo_id, count(p.id) as photo_count, v.itemid as vendor_itemid, v.title as vendortitle, m.itemid as model_itemid, m.title as modeltitle, c.title as colortitle,
 cur.sign as currency_sign, a.price*cur.rate as price_in_rur, DATE_FORMAT(a.createDate, '%a, %d %b %Y %T GMT') as rfcDate, ct.title as city_title, r.title as region_title
 FROM jos_autobb_messages AS a
 LEFT JOIN jos_autobb_photos AS p
 ON a.id=p.msgid
 LEFT JOIN jos_autobb_vendors AS v
 ON a.vendor=v.id
 LEFT JOIN jos_autobb_models AS m
 ON a.model=m.id
 LEFT JOIN jos_autobb_colors AS c
 ON a.color=c.id
 LEFT JOIN jos_autobb_currency AS cur
 ON a.currency=cur.id
 LEFT JOIN jos_autobb_cities AS ct
 ON a.city=ct.id
 LEFT JOIN jos_autobb_regions AS r
 ON ct.region=r.id
 WHERE a.expirationDate>=NOW() AND a.published=1
 GROUP BY a.id
 ORDER BY a.sticked DESC, a.ordering ASC, a.CreateDate DESC
 LIMIT 0, 10

Посоветуйте, как быть?

PHP_Master · 26 Апр 2009

У тебя сами база и скрипт спроектированы через одно место, если для такой банальщины
как "запрос на выборку списка объявленийs необходимо объединять 8 таблиц.

dmsoh · 26 Апр 2009

PHP_Master написал(а):
У тебя сами база и скрипт спроектированы через одно место, если для такой банальщины
как "запрос на выборку списка объявленийs необходимо объединять 8 таблиц.

Понимаю, но как говориться что имеем, то имеем. 8 таблиц - это и таблица городов, и марок и моделей, и фотографий и т.п. и без них никак не обойтись. Это все в списке нужно отобразить.

Но может подскажите, как можно облегчить нагрузку?

MadWizard · 26 Апр 2009

Без структуры таблиц трудно ставить диагноз.

Навскидку, проверь, чтобы на всех полях условий и сортировки стояли индексы: a.expirationDate, a.published, a.id, a.sticked, a.ordering, a.CreateDate.
Следующий шаг - индексы на полях, по которым джойнишь.

dmsoh · 26 Апр 2009

MadWizard написал(а):
Без структуры таблиц трудно ставить диагноз.

Навскидку, проверь, чтобы на всех полях условий и сортировки стояли индексы: a.expirationDate, a.published, a.id, a.sticked, a.ordering, a.CreateDate.
Следующий шаг - индексы на полях, по которым джойнишь.

Спасибо за совет, индексы добавлю.

Вот структура таблиц

PHP:

		CREATE TABLE IF NOT EXISTS `#__autobb_bodies` (
		  `id` int(11) NOT NULL auto_increment,
		  `category` int(11) NOT NULL default '0',
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 


		CREATE TABLE IF NOT EXISTS `#__autobb_cities` (
		  `id` int(11) NOT NULL auto_increment,
		  `title` varchar(255) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `english_title` varchar(255) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `region` int(11) NOT NULL default '0',
		  `ismain` tinyint(1) NOT NULL default '0',
		  `metro` tinyint(1) NOT NULL default '0',
		  `prefix` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `gismeteo_code` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `ordering` int(11) NOT NULL default '0',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 


		CREATE TABLE IF NOT EXISTS `#__autobb_colors` (
		  `id` int(11) NOT NULL auto_increment,
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `ordering` int(11) NOT NULL default '0',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 
	
	
		CREATE TABLE IF NOT EXISTS `#__autobb_complectations` (
		  `id` int(11) NOT NULL auto_increment,
		  `category` int(11) NOT NULL default '0',
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `options` varchar(255) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 
	 
	 
		CREATE TABLE `#__autobb_config` (
  `id` int(11) NOT NULL auto_increment,
  `category` int(11) NOT NULL default '0',
  `show_eula` tinyint(1) NOT NULL default '0',
  `eula` text NOT NULL,
  `addads` tinyint(1) NOT NULL default '1',
  `allow_guestpost` tinyint(1) NOT NULL default '0',
  `autopublish` tinyint(1) NOT NULL default '0',
  `price_list` varchar(255) NOT NULL default '',
  `mileage_list` varchar(255) NOT NULL default '',
  `volume_list` varchar(255) NOT NULL default '',
  `city_popup_width` int(11) NOT NULL default '0',
  `city_popup_height` int(11) NOT NULL default '0',
  `default_mileage_unit` tinyint(1) NOT NULL default '0',
  `default_speed_unit` tinyint(1) NOT NULL default '0',
  `antiflood` int(11) NOT NULL default '3600',
  `allow_jcomments` tinyint(1) NOT NULL default '0',
  `allow_css` tinyint(1) NOT NULL default '0',
  `css_sfx` varchar(100) NOT NULL default '',
  `show_info_owner` tinyint(1) NOT NULL default '0',
  `state_new` tinyint(3) NOT NULL default '10',
  `vendor_description_show` int(11) NOT NULL default '0',
  `model_description_show` int(11) NOT NULL default '0',
  `write_access` int(1) NOT NULL default '0',
  `lang_sfx` varchar(100) NOT NULL default '',
  `allow_search_complekts` tinyint(1) NOT NULL default '0',
  `extsearch_lifetime` int(11) NOT NULL default '0',
  `color_sort` tinyint(1) NOT NULL default '0',
  `foto_show_sort` tinyint(1) NOT NULL default '0',
  `defaultDuring` int(11) NOT NULL default '0',
  `allow_stats` tinyint(1) NOT NULL default '0',
  `photos_maxnum` int(11) NOT NULL default '0',
  `photos_maxsize` int(11) NOT NULL default '0',
  `photos_maxwidth` int(11) NOT NULL default '0',
  `photos_maxheight` int(11) NOT NULL default '0',
  `photos_quality` int(11) NOT NULL default '0',
  `photos_savequality` int(11) NOT NULL default '0',
  `thumb_maxsize` int(11) NOT NULL default '0',
  `thumb_quality` int(11) NOT NULL default '0',
  `middle_maxsize` int(11) NOT NULL default '0',
  `middle_quality` int(11) NOT NULL default '0',
  `custom_maxsize` int(11) NOT NULL default '0',
  `custom_quality` int(11) NOT NULL default '0',
  `put_logo` tinyint(1) NOT NULL default '0',
  `logo_position` int(11) NOT NULL default '0',
  `logo_sfx` varchar(100) NOT NULL default '',
  `allow_fullsize` tinyint(1) NOT NULL default '0',
  `photos_allow_reload` tinyint(1) NOT NULL default '0',
  `photos_antialias` tinyint(1) NOT NULL default '0',
  `year_maxnum` int(11) NOT NULL default '0',
  `cachetime_show` int(11) NOT NULL default '0',
  `cachetime_photo` int(11) NOT NULL default '0',
  `show_name` tinyint(1) NOT NULL default '0',
  `show_company` tinyint(1) NOT NULL default '0',
  `show_additional` tinyint(1) NOT NULL default '0',
  `show_email` tinyint(1) NOT NULL default '0',
  `show_phone1` tinyint(1) NOT NULL default '0',
  `show_phone2` tinyint(1) NOT NULL default '0',
  `show_phone3` tinyint(1) NOT NULL default '0',
  `show_city` tinyint(1) NOT NULL default '0',
  `show_price` tinyint(1) NOT NULL default '0',
  `show_modification` tinyint(1) NOT NULL default '0',
  `show_color` tinyint(1) NOT NULL default '0',
  `show_year` tinyint(1) NOT NULL default '0',
  `show_bodytype` tinyint(1) NOT NULL default '0',
  `show_engine` tinyint(1) NOT NULL default '0',
  `show_fuelinputcity` tinyint(1) NOT NULL default '0',
  `show_fuelinputcountry` tinyint(1) NOT NULL default '0',
  `show_fuelinputboth` tinyint(1) NOT NULL default '0',
  `show_mileage` tinyint(1) NOT NULL default '0',
  `show_drive` tinyint(1) NOT NULL default '0',
  `show_volume` tinyint(1) NOT NULL default '0',
  `show_power` tinyint(1) NOT NULL default '0',
  `show_speed` tinyint(1) NOT NULL default '1',
  `show_transmission` tinyint(1) NOT NULL default '0',
  `show_state` tinyint(1) NOT NULL default '0',
  `show_wrangle` tinyint(1) NOT NULL default '0',
  `show_steering_wheel` tinyint(1) NOT NULL default '0',
  `show_vin` tinyint(1) NOT NULL default '0',
  `show_customs` tinyint(1) NOT NULL default '0',
  `show_photos` tinyint(1) NOT NULL default '0',
  `show_complectation` tinyint(1) NOT NULL default '0',
  `show_createdate` tinyint(1) NOT NULL default '0',
  `show_expirationdate` tinyint(1) NOT NULL default '0',
  `seo_words` text NOT NULL,
  `cachetime_seomap` int(11) NOT NULL default '2592000',
  `allow_rss` tinyint(1) NOT NULL default '0',
  `rss_title` varchar(255) NOT NULL default '',
  `rss_description` varchar(255) NOT NULL default '',
  `rss_managingEditor` varchar(255) NOT NULL default '',
  `rss_webMaster` varchar(255) NOT NULL default '',
  `rss_image_url` varchar(255) NOT NULL default '',
  `rss_image_title` varchar(255) NOT NULL default '',
  `show_link_add` tinyint(1) NOT NULL default '1',
  `show_link_search` tinyint(1) NOT NULL default '1',
  `show_link_date` tinyint(1) NOT NULL default '1',
  `show_link_mil` tinyint(1) NOT NULL default '1',
  `show_link_new` tinyint(1) NOT NULL default '1',
  `show_city_list` tinyint(1) NOT NULL default '1',
  `show_date_pub` tinyint(1) NOT NULL default '1',
  `show_search_form` tinyint(1) NOT NULL default '1',
  `show_views` tinyint(1) NOT NULL default '1',
  `show_status` tinyint(1) NOT NULL default '0',
  `revcats` tinyint(1) NOT NULL default '1',
  `revc_cat_cor` int(11) NOT NULL default '601',
  PRIMARY KEY  (`id`)
) Type=MyISAM  DEFAULT CHARSET=cp1251;
	 
	 
		CREATE TABLE IF NOT EXISTS `#__autobb_currency` (
		  `id` int(11) NOT NULL auto_increment,
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `sign` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `rate` float NOT NULL default '0',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 
	 
	 
		CREATE TABLE IF NOT EXISTS `#__autobb_extsearch` (
		  `id` int(11) NOT NULL auto_increment,
		  `results` text CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
		  `searchParam` text CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
		  `expirationDate` datetime NOT NULL default '0000-00-00 00:00:00',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 
	  
	  
		CREATE TABLE `#__autobb_messages` (
  `id` int(11) NOT NULL auto_increment,
  `createDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `modifyDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `expirationDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `sticked` tinyint(1) NOT NULL default '0',
  `ordering` int(11) NOT NULL default '0',
  `front` tinyint(1) NOT NULL default '0',
  `published` tinyint(1) NOT NULL default '1',
  `name` varchar(100) NOT NULL default '',
  `company` varchar(100) NOT NULL default '',
  `phone1` varchar(100) NOT NULL default '',
  `since1` int(11) NOT NULL default '0',
  `till1` int(11) NOT NULL default '0',
  `phone2` varchar(100) NOT NULL default '',
  `since2` int(11) NOT NULL default '0',
  `till2` int(11) NOT NULL default '0',
  `phone3` varchar(100) NOT NULL default '',
  `since3` int(11) NOT NULL default '0',
  `till3` int(11) NOT NULL default '0',
  `email` varchar(255) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  `city` int(11) NOT NULL default '0',
  `userid` int(11) NOT NULL default '0',
  `category` int(11) NOT NULL default '0',
  `vendor` int(11) NOT NULL default '0',
  `model` int(11) NOT NULL default '0',
  `modification` varchar(100) NOT NULL default '',
  `color` int(11) NOT NULL default '0',
  `year` int(11) NOT NULL default '0',
  `bodytype` int(11) NOT NULL default '0',
  `price` int(11) NOT NULL default '0',
  `currency` int(11) NOT NULL default '0',
  `engine` int(11) NOT NULL default '0',
  `fuelinputcity` decimal(4,1) NOT NULL default '0.0',
  `fuelinputcountry` decimal(4,1) NOT NULL default '0.0',
  `fuelinputboth` decimal(4,1) NOT NULL default '0.0',
  `mileage` int(11) NOT NULL default '0',
  `mileage_unit` int(11) NOT NULL default '0',
  `drive` int(11) NOT NULL default '0',
  `volume` int(11) NOT NULL default '0',
  `power` int(11) NOT NULL default '0',
  `speed` int(3) NOT NULL default '0',
  `transmission` int(11) NOT NULL default '0',
  `state` int(11) NOT NULL default '0',
  `steering_wheel` tinyint(1) NOT NULL default '0',
  `wrangle` tinyint(1) NOT NULL default '0',
  `vin` varchar(100) NOT NULL default '',
  `vin2` varchar(100) NOT NULL default '',
  `customs` tinyint(1) NOT NULL default '0',
  `additional` text,
  `views` int(11) NOT NULL default '0',
  `en` tinyint(1) NOT NULL default '0',
  `cr` tinyint(1) NOT NULL default '0',
  `lastViewIP` varchar(20) default NULL,
  `ip` varchar(20) default NULL,
  `banned` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `createDate` (`createDate`),
  KEY `expirationDate` (`expirationDate`),
  KEY `vendor` (`vendor`),
  KEY `model` (`model`),
  KEY `city` (`city`),
  KEY `state` (`state`)
) Type=MyISAM  DEFAULT CHARSET=cp1251;
	
	
		CREATE TABLE IF NOT EXISTS `#__autobb_models` (
		  `id` int(11) NOT NULL auto_increment,
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `vendor` int(11) NOT NULL default '0',
		  `itemid` int(11) NOT NULL default '0',
		  `description` text CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 

		CREATE TABLE IF NOT EXISTS `#__autobb_msg_complect` (
		  `id` int(11) NOT NULL auto_increment,
		  `msgid` int(11) NOT NULL default '0',
		  `complectid` int(11) NOT NULL default '0',
		  `options` int(11) NOT NULL default '0',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 
	 
	 
		CREATE TABLE IF NOT EXISTS `#__autobb_photos` (
		  `id` int(11) NOT NULL auto_increment,
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `msgid` int(11) NOT NULL default '0',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 


		CREATE TABLE IF NOT EXISTS `#__autobb_regions` (
		  `id` int(11) NOT NULL auto_increment,
		  `title` varchar(255) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 
	
	
		CREATE TABLE IF NOT EXISTS `#__autobb_states` (
		  `id` int(11) NOT NULL auto_increment,
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `ordering` int(11) NOT NULL default '0',
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251; 

		CREATE TABLE IF NOT EXISTS `#__autobb_vendors` (
		  `id` int(11) NOT NULL auto_increment,
		  `category` int(11) NOT NULL default '0',
		  `title` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
		  `itemid` int(11) NOT NULL default '0',
		  `description` text CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
		  PRIMARY KEY  (`id`)
		) Type=MyISAM  DEFAULT CHARSET=cp1251;

MadWizard · 26 Апр 2009

Так и есть. Нужно добавить индексы на поля color, currency, published, sticked, ordering, cities.region, photos.msgid.

Если других запросов не предполагается, для messages можно сделать один составной индекс из этих полей.

dmsoh · 26 Апр 2009

MadWizard написал(а):
Так и есть. Нужно добавить индексы на поля color, currency, published, sticked, ordering, cities.region, photos.msgid.

Последовал вашему совету.

Это немного помогло. Т.е. Вместо 11 сек в этом запросе, уже можно видеть 4-5 сек. Общее время генерации уменьшилось. 8-9 сек, однако все-равно тяжело.

PHP_Master · 26 Апр 2009

Чудес не бывает - только в сказке.
Хочешь нормальной скорости - меняй структуру.
Джоин 8-ми таблиц для вывода объявлений - это конкретный перебор.

Simpson · 26 Апр 2009

а мне кажется, что тут проблема:
GROUP BY
ORDER BY

что explain каже?

MadWizard · 26 Апр 2009

dmsoh написал(а):
Последовал вашему совету.

Это немного помогло. Т.е. Вместо 11 сек в этом запросе, уже можно видеть 4-5 сек. Общее время генерации уменьшилось. 8-9 сек, однако все-равно тяжело.

Тогда нужно определить, где главные тормоза: при выборке, джойне, группировке или сортировке.
Сначала сделать SELECT a.id FROM ..... чтобы понять, что это тормозят не вычисления в селекте.
Потом по очереди убирать джойны, группировки, сортировки и смотреть, где будет скачок производительности. Т.е. убрать последний джойн, потом группировку, потом сортировку; убрать предпоследний джойн и т.д.

Когда время резко скакнет, будет видно какая таблица тормозит.

Оптимизация запроса

dmsoh

Профессор

PHP_Master

Хранитель порядка

dmsoh

Профессор

MadWizard

Постоялец

dmsoh

Профессор

MadWizard

Постоялец

dmsoh

Профессор

PHP_Master

Хранитель порядка

Simpson

Профессор

MadWizard

Постоялец