Создатель
Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва
Репутация: нет
Всего: 8
|
Таблицы: | Код | CREATE TABLE `cache_price` (
`id_price` bigint(20) unsigned NOT NULL,
`order` double unsigned NOT NULL,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`id_step` char(40) NOT NULL,
`id_session` char(40) NOT NULL,
`id_firm` bigint(20) unsigned NOT NULL DEFAULT '0',
`id_service` bigint(20) unsigned NOT NULL DEFAULT '0',
`id_city` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id_price`,`id_step`,`id_session`,`id_firm`,`id_service`,`id_city`),
KEY `index_10` (`id_service`),
KEY `index_11` (`id_price`),
KEY `index_4` (`id_step`),
KEY `index_5` (`id_session`),
KEY `index_6` (`id_firm`),
KEY `Index_7` (`order`),
KEY `index_8` (`id_city`),
KEY `index_9` (`id_price`,`id_step`,`id_session`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
CREATE TABLE `price` (
`id_price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Первичный ключ',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'Наименование',
`id_firm` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Код фирмы',
`id_city` bigint(20) unsigned NOT NULL DEFAULT '0',
`id_producer_goods` bigint(20) unsigned NOT NULL DEFAULT '0',
`id_group` bigint(20) unsigned NOT NULL DEFAULT '0',
`id_subgroup` bigint(20) unsigned NOT NULL,
`id_producer_country` bigint(20) unsigned NOT NULL,
`manufacture` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`unit` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`pack` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`info` varchar(4000) CHARACTER SET utf8 DEFAULT NULL,
`cost1` decimal(20,2) DEFAULT NULL,
`currency_name1` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`sale_name1` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`cost2` decimal(20,2) DEFAULT NULL,
`currency_name2` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`sale_name2` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`id_service` bigint(20) unsigned NOT NULL,
`small_image_exist` tinyint(1) DEFAULT '0',
`big_image_exist` tinyint(1) NOT NULL DEFAULT '0',
`id_type` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id_price`,`id_service`,`id_city`,`id_firm`) USING BTREE,
UNIQUE KEY `index_24` (`id_price`) USING BTREE,
KEY `Index_3` (`id_firm`),
KEY `index_5` (`id_city`),
KEY `index_15` (`id_producer_goods`),
KEY `index_6` (`id_group`),
KEY `index_7` (`id_subgroup`),
KEY `index_8` (`id_producer_country`),
KEY `index_16` (`currency_name1`),
KEY `index_17` (`sale_name1`),
KEY `index_18` (`cost2`),
KEY `index_19` (`currency_name2`),
KEY `index_20` (`sale_name2`),
KEY `index_22` (`id_service`),
KEY `Index_33` (`id_type`),
KEY `index_25` (`id_price`,`id_firm`,`id_city`,`id_producer_country`),
FULLTEXT KEY `index_4` (`name`),
FULLTEXT KEY `index_11` (`manufacture`),
FULLTEXT KEY `index_12` (`unit`),
FULLTEXT KEY `index_13` (`pack`),
FULLTEXT KEY `index_14` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
CREATE TABLE `firm` (
`id_firm` bigint(20) unsigned NOT NULL,
`id_city` bigint(20) unsigned NOT NULL,
`id_region_country` bigint(20) unsigned NOT NULL,
`id_country` bigint(20) unsigned NOT NULL,
`id_region_city` bigint(20) unsigned DEFAULT NULL,
`producer` varchar(255) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`jure` varchar(255) DEFAULT NULL,
`inn` varchar(255) DEFAULT NULL,
`business` varchar(255) DEFAULT NULL,
`zip` varchar(255) DEFAULT NULL,
`path` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`mode_work` varchar(1024) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`fax` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`web` varchar(255) DEFAULT NULL,
`pager` varchar(255) DEFAULT NULL,
`info` varchar(5000) DEFAULT NULL,
`id_parent` bigint(20) unsigned DEFAULT NULL,
`count` bigint(20) unsigned DEFAULT NULL,
`big_image_exists` tinyint(1) DEFAULT NULL,
`small_image_exists` tinyint(1) DEFAULT NULL,
`id_service` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id_firm`,`id_city`,`id_service`) USING BTREE,
UNIQUE KEY `index_26` (`id_firm`) USING BTREE,
KEY `index_2` (`id_city`),
KEY `index_3` (`id_region_country`),
KEY `index_4` (`id_country`),
KEY `index_5` (`id_region_city`),
KEY `index_9` (`inn`),
KEY `index_11` (`zip`),
KEY `index_14` (`mode_work`(1000)),
KEY `index_15` (`phone`),
KEY `index_16` (`fax`),
KEY `index_17` (`email`),
KEY `index_18` (`web`),
KEY `index_19` (`pager`),
KEY `index_21` (`id_parent`),
KEY `index_22` (`count`),
KEY `index_23` (`big_image_exists`),
KEY `index_24` (`small_image_exists`),
KEY `index_25` (`id_service`),
KEY `index_27` (`id_city`),
KEY `index_28` (`id_service`),
FULLTEXT KEY `index_6` (`producer`),
FULLTEXT KEY `index_7` (`name`),
FULLTEXT KEY `index_8` (`jure`),
FULLTEXT KEY `index_10` (`business`),
FULLTEXT KEY `index_12` (`path`),
FULLTEXT KEY `index_13` (`address`),
FULLTEXT KEY `index_20` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
CREATE TABLE `firm_adv` (
`id_firm` bigint(20) unsigned NOT NULL,
`order` varchar(45) NOT NULL,
`title` varchar(255) DEFAULT NULL,
`html` blob,
`active` tinyint(1) NOT NULL DEFAULT '1',
`partners` blob,
`video` blob,
PRIMARY KEY (`id_firm`,`order`) USING BTREE,
KEY `firm_adv_index01` (`id_firm`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
CREATE TABLE `city` (
`id_city` bigint(20) unsigned NOT NULL,
`id_city_type` bigint(20) unsigned DEFAULT NULL,
`id_arial_region` bigint(20) unsigned NOT NULL,
`id_country` bigint(20) unsigned NOT NULL,
`id_region_country` bigint(20) unsigned NOT NULL,
`population` bigint(20) unsigned DEFAULT NULL,
`code` varchar(255) DEFAULT NULL,
`code_region` varchar(255) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id_city`),
KEY `index_3` (`id_city_type`),
KEY `index_4` (`id_arial_region`),
KEY `index_5` (`id_country`),
KEY `index_6` (`id_region_country`),
KEY `index_7` (`population`),
KEY `index_8` (`code`),
KEY `index_9` (`code_region`),
KEY `index_10` (`name`),
FULLTEXT KEY `index_2` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `city_type` (
`id_city_type` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id_city_type`),
KEY `index_2` (`name`),
FULLTEXT KEY `index_3` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
CREATE TABLE `producer_country` (
`id_producer_country` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id_producer_country`),
KEY `index_2` (`name`),
FULLTEXT KEY `index_3` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `cache_basket` (
`id_session` char(40) NOT NULL,
`id_price` bigint(20) NOT NULL DEFAULT '-1',
`id_firm` bigint(20) NOT NULL DEFAULT '-1',
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`id_service` bigint(20) unsigned NOT NULL DEFAULT '0',
`id_city` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id_session`,`id_price`,`id_firm`,`id_service`,`id_city`),
KEY `index_2` (`id_session`),
KEY `index_3` (`id_price`),
KEY `index_4` (`id_firm`),
KEY `index_5` (`datetime`),
KEY `Index_6` (`id_service`),
KEY `Index_7` (`id_city`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED;
CREATE TABLE `firm_settings` (
`id_firm` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`dont_show_map` tinyint(1) NOT NULL DEFAULT '1',
`static_map` text NOT NULL,
`default_page` varchar(255) NOT NULL DEFAULT '/data/firm',
`site_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id_firm`),
UNIQUE KEY `index_4` (`site_name`),
KEY `index_2` (`dont_show_map`) USING BTREE,
KEY `index_3` (`default_page`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=57755 DEFAULT CHARSET=cp1251; |
Количество записей в таблицах: cache_price: 18e5 price: 5e5 firm: 4e4 В остальных - в пределах 20 записей. Запрос: | Код |
select sql_calc_found_rows
p.`id_firm`,
p.`id_price`,
p.name as `price_name`,
f.name as `firm_name`,
pc.name as `producer_country_name`,
`p`.`unit` AS `price_unit`,
`f`.`phone` AS `firm_phone`,
`cp`.`id_session` AS `id_session`,
`cp`.`id_step` AS `id_step`,
`cp`.`order` AS `order`,
`p`.`id_producer_goods`,
`p`.`manufacture` AS `manufacture`,
`p`.`info` AS `info`,
format(`p`.`cost1`,2) AS `cost1`,
format(`p`.`cost2`,2) AS `cost2`,
`p`.`currency_name1` AS `currency_name1`,
`p`.`currency_name2` AS `currency_name2`,
`p`.`sale_name1` AS `sale_name1`,
`p`.`sale_name2` AS `sale_name2`,
c.code as `phone_code`,
c.name as `city_name`,
ct.name as `city_type_name`,
cb.id_price as price_in_basket,
`p`.big_image_exist as image_exist,
fs.site_name as site_name,
active as `adv`
from
cache_price cp
inner join price p on
cp.id_price=p.id_price
inner join firm f on
p.id_firm=f.id_firm
left outer join firm_adv fa on
fa.id_firm=f.id_firm
inner join city c on
p.id_city=c.id_city
inner join city_type ct on
ct.id_city_type=c.id_city_type
inner join producer_country pc on
`p`.`id_producer_country` = `pc`.`id_producer_country`
left outer join cache_basket cb on
cb.id_price=cp.id_price and
cb.id_session=cp.id_session
left outer join firm_settings fs on
f.id_firm=fs.id_firm
where
cp.id_session='872fe17b6af944f0b51971f35fc67cc67944869c' and
cp.`id_step`='1e9e2790624940da11e74a8f823f46e314d0cdcb'
order by
cp.`order` desc
limit
0, 25
|
Explain по запросу: | Код | id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cp index PRIMARY,index_11,index_4,index_5,index_9 Index_7 8 2023965 Using where; Using index
1 SIMPLE cb ref PRIMARY,index_2,index_3 PRIMARY 48 const,ratiss.cp.id_price 1 Using index
1 SIMPLE p eq_ref PRIMARY,index_24,Index_3,index_5,index_8,index_25 index_24 8 ratiss.cp.id_price 1 (null)
1 SIMPLE pc eq_ref PRIMARY PRIMARY 8 ratiss.p.id_producer_country 1 (null)
1 SIMPLE c eq_ref PRIMARY,index_3 PRIMARY 8 ratiss.p.id_city 1 (null)
1 SIMPLE ct eq_ref PRIMARY PRIMARY 8 ratiss.c.id_city_type 1 (null)
1 SIMPLE f eq_ref PRIMARY,index_26 index_26 8 ratiss.p.id_firm 1 (null)
1 SIMPLE fs eq_ref PRIMARY PRIMARY 8 ratiss.p.id_firm 1 (null)
1 SIMPLE fa ref PRIMARY,firm_adv_index01 PRIMARY 8 ratiss.f.id_firm 1 (null)
|
Запрос жутко тормозит (может выполняться и сутки), если параллельно выполняется ещё несколько (4-5) таких же. Часть настроек MySQL: | Код |
max_connections=1510
query_cache_size=168M
table_cache=3020
tmp_table_size=30M
thread_cache_size=64
myisam_max_sort_file_size=1M
myisam_sort_buffer_size=60M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=1M
sort_buffer_size=2M
innodb_additional_mem_pool_size=11M
innodb_log_buffer_size=6M
innodb_buffer_pool_size=500M
innodb_log_file_size=100M
innodb_thread_concurrency=18
|
Ресурсов предостаточно. Может, что посоветуете? |
Тормозит запрос, MySQL 5.1.32 
Загрузка ...
)) 
