участвующие таблицы
CREATE TABLE IF NOT EXISTS nomenclature_confornity(
id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ИД',
irs_section_id INT(11) NOT NULL COMMENT 'ИД раздела ИРС',
irs_pos_id INT(11) NOT NULL COMMENT 'ИД позиции ИРС',
not_irs_pos_id INT(11) NOT NULL COMMENT 'ИД позиции для соответствия',
koef DOUBLE NOT NULL COMMENT 'Коэффициент похожести',
conf_id INT(11) NOT NULL DEFAULT -1 COMMENT 'ИД соответствия из таблицы conformities',
PRIMARY KEY (id),
INDEX IX_nomenclature_confornity (irs_pos_id, not_irs_pos_id),
INDEX IX_nomenclature_confornity_irs_pos_id (irs_pos_id),
INDEX IX_nomenclature_confornity_irs_section_id (irs_section_id),
INDEX IX_nomenclature_confornity_not_irs_pos_id (not_irs_pos_id),
INDEX UK_nomenclature_confornity_conf_id (conf_id),
UNIQUE INDEX UK_nomenclature_confornity_id (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 368301
AVG_ROW_LENGTH = 52
CHARACTER SET cp1251
COLLATE cp1251_general_ci;
CREATE TABLE IF NOT EXISTS section_stats(
id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ИД',
irs_section_id INT(11) NOT NULL COMMENT 'ИД раздела ИРС',
word_id INT(11) NOT NULL COMMENT 'ИД слова',
word_count INT(11) NOT NULL DEFAULT 1 COMMENT 'Количество слов в разделе',
word_coef DOUBLE NOT NULL DEFAULT 0 COMMENT 'Вес слова в разделе',
PRIMARY KEY (id),
INDEX IX_section_stats_irs_section_id (irs_section_id),
INDEX IX_section_stats_word_id (word_id),
UNIQUE INDEX UK_section_stats (irs_section_id, word_id),
UNIQUE INDEX UK_section_stats_id (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 68049
AVG_ROW_LENGTH = 53
CHARACTER SET cp1251
COLLATE cp1251_general_ci
COMMENT = 'Статистика слов по разделам';
CREATE TABLE IF NOT EXISTS string_words(
id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ИД',
nom_id INT(11) NOT NULL COMMENT 'ИД номенклатуры',
word_id INT(11) NOT NULL COMMENT 'ИД слова',
PRIMARY KEY (id),
INDEX IX_string_words_nom_id (nom_id),
INDEX IX_string_words_word_id (word_id),
UNIQUE INDEX UK_string_words_id (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1203296
AVG_ROW_LENGTH = 33
CHARACTER SET cp1251
COLLATE cp1251_general_ci
COMMENT = 'Таблица принадлежности слов номенклатуре';
есть такой update
update nomenclature_confornity nc
set nc.koef = (select ifnull(sum(ss.word_coef),0) from section_stats ss
where ss.irs_section_id = 447 and ss.word_id in
(select word_id from string_words force index (IX_string_words_nom_id)
where nom_id = nc.not_irs_pos_id)
and ss.word_id in
(select word_id from string_words force index (IX_string_words_nom_id)
where nom_id = nc.irs_pos_id)
)
where nc.irs_section_id = 447 and nc.conf_id = -1
;
explain
select nc.not_irs_pos_id, nc.irs_pos_id,
(select ifnull(sum(ss.word_coef),0) from section_stats ss
where ss.irs_section_id = 447 and ss.word_id in
(select word_id from string_words force index (IX_string_words_nom_id)
where nom_id = nc.not_irs_pos_id)
and ss.word_id in
(select word_id from string_words force index (IX_string_words_nom_id)
where nom_id = nc.irs_pos_id)
)
from nomenclature_confornity nc
where nc.irs_section_id = 447 and nc.conf_id = -1
;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY nc ref IX_nomenclature_confornity_irs_section_id,UK_nomenclature_confornity_conf_id IX_nomenclature_confornity_irs_section_id 4 const 225 Using where
2 DEPENDENT SUBQUERY ss ref UK_section_stats,IX_section_stats_irs_section_id UK_section_stats 4 const 30 Using where
4 DEPENDENT SUBQUERY string_words ref IX_string_words_nom_id IX_string_words_nom_id 4 price.nc.irs_pos_id 5 Using where
3 DEPENDENT SUBQUERY string_words ref IX_string_words_nom_id IX_string_words_nom_id 4 price.nc.not_irs_pos_id 5 Using where
Отредактированно PraporGosha (23.03.2011 10:42:30)
