Re: performance issue
Posted by:
Muneera h
Date: February 28, 2015 11:50PM
Thank you Rick for your reply.
SHOW CREATE TABLE :
CREATE TABLE `file_2grams` (
`id_file_2grams` int(11) NOT NULL AUTO_INCREMENT,
`file_control_id_file_control` int(10) NOT NULL,
`2grams_id_2grams` int(11) NOT NULL,
`file_2grams_freq` int(11) NOT NULL,
`text_time_period_id_text_time_period` int(11) NOT NULL,
`text_topic_text_medium_id_text_medium` int(11) NOT NULL,
`text_topic_text_domain_id_text_domain` int(11) NOT NULL,
`text_topic_id_text_topic` int(11) NOT NULL,
`region_country_id_region_country` int(11) NOT NULL,
`text_authors_id_text_authors` int(11) NOT NULL,
PRIMARY KEY (`id_file_2grams`,`file_control_id_file_control`,`2grams_id_2grams`),
UNIQUE KEY `id_file_2grams_UNIQUE` (`id_file_2grams`),
KEY `fk_file_2grams_file_control1_idx` (`file_control_id_file_control`),
KEY `fk_file_2grams_2grams1_idx` (`2grams_id_2grams`),
KEY `file_2grams_freq` (`file_2grams_freq`),
KEY `text_time_period_id_text_time_period` (`text_time_period_id_text_time_period`,`text_topic_text_medium_id_text_medium`,`text_topic_text_domain_id_text_domain`,`text_topic_id_text_topic`,`region_country_id_region_country`,`text_authors_id_text_authors`),
KEY `text_topic_text_medium_id_text_medium` (`text_topic_text_medium_id_text_medium`),
KEY `text_topic_id_text_topic` (`text_topic_id_text_topic`),
KEY `text_topic_text_domain_id_text_domain` (`text_topic_text_domain_id_text_domain`),
KEY `region_country_id_region_country` (`region_country_id_region_country`),
KEY `text_authors_id_text_authors` (`text_authors_id_text_authors`),
CONSTRAINT `fk_file_2grams_2grams1` FOREIGN KEY (`2grams_id_2grams`) REFERENCES `2grams` (`id_2grams`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_file_2grams_file_control1` FOREIGN KEY (`file_control_id_file_control`) REFERENCES `file_control` (`id_file_control`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=545758866 DEFAULT CHARSET=utf8 COMMENT='This table is used to store the unique bigrams and their frequency in each file'
EXPLAIN:
explain SELECT sum(file_2grams_freq),2grams_id_2grams From file_2grams
where text_topic_text_medium_id_text_medium = 2
group by 2grams_id_2grams
limit 100
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE file_2grams index PRIMARY,id_file_2grams_UNIQUE,fk_file_2grams_file_control1_idx,fk_file_2grams_2grams1_idx,file_2grams_freq,text_time_period_id_text_time_period,text_topic_text_medium_id_text_medium,text_topic_id_text_topic,text_topic_text_domain_id_text_domain,region_country_id_region_country,text_authors_id_text_authors fk_file_2grams_2grams1_idx 4 NULL 400 Using where
my application is a corpus contain tool to calculate (concordance, collocation ...etc)