Re: performance issue

[Muneera h]

Thanks, that's work!

Ok here is another query:

select count(id_text_meta_data), text_topic_text_medium_id_text_medium From text_meta_data where id_text_meta_data IN(select text_meta_data_id_text_meta_data from file_control where id_file_control IN
(SELECT file_control_id_file_control FROM file_2grams WHERE 2grams_id_2grams= 90983))group by text_topic_text_medium_id_text_medium


Explain:

1 SIMPLE file_2grams ref fk_file_2grams_file_control1_idx,fk_file_2grams_2grams1_idx fk_file_2grams_2grams1_idx 4 const 5802 Using index; Using temporary; Using filesort; Start temporary
1 SIMPLE file_control ref PRIMARY,id_file_control_UNIQUE,IXtext_meta_data_id,fk_file_control_text_meta_data1_idx PRIMARY 4 kacstac_dbo.file_2grams.file_control_id_file_control 1 Using index
1 SIMPLE text_meta_data ref PRIMARY,id_text_meta_data_UNIQUE,IX2,IX4,FK_text_meta_data_text_authors,text_title,text_year_published,total_no_parts,FK_text_language_id_text_language,FK_text_topic_id_text_topic PRIMARY 4 kacstac_dbo.file_control.text_meta_data_id_text_meta_data 1 Using index; End temporary




TABLE `file_control` (
`id_file_control` int(10) NOT NULL AUTO_INCREMENT,
`text_meta_data_id_text_meta_data` int(10) NOT NULL,
`file_name_as_stored` varchar(128) NOT NULL,
`file_part` int(10) NOT NULL,
`file_size` int(10) NOT NULL,
`file_path` varchar(255) NOT NULL,
`file_date_added` datetime NOT NULL,
`file_content` longtext NOT NULL,
`flag1` int(11) NOT NULL DEFAULT '0',
`flag2` int(11) NOT NULL DEFAULT '0',
`flag3` int(11) NOT NULL DEFAULT '0',
`flag4` int(11) NOT NULL DEFAULT '0',
`flag5` int(11) NOT NULL DEFAULT '0',
`clean_flag` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id_file_control`,`text_meta_data_id_text_meta_data`),
KEY `IXtext_meta_data_id` (`text_meta_data_id_text_meta_data`),
KEY `flag1` (`flag1`),
KEY `flag2` (`flag2`),
KEY `flag3` (`flag3`),
KEY `flag4` (`flag4`),
KEY `flag5` (`flag5`),
KEY `file_part` (`file_part`),
KEY `file_date_added` (`file_date_added`),
KEY `fk_file_control_text_meta_data1_idx` (`text_meta_data_id_text_meta_data`),
FULLTEXT KEY `file_content` (`file_content`)
) ENGINE=InnoDB AUTO_INCREMENT=1076553 DEFAULT CHARSET=utf8 COMMENT='This table is used to store the necessary information about text/File parts'



TABLE `text_meta_data` (
`id_text_meta_data` int(10) NOT NULL AUTO_INCREMENT,
`text_language_id_text_language` int(11) NOT NULL,
`text_time_period_id_text_time_period` int(10) NOT NULL,
`text_topic_id_text_topic` int(10) NOT NULL,
`text_topic_text_domain_id_text_domain` int(10) NOT NULL,
`text_topic_text_medium_id_text_medium` int(10) NOT NULL,
`region_country_id_region_country` int(10) NOT NULL,
`text_authors_id_text_authors` int(10) NOT NULL,
`text_title` varchar(255) NOT NULL,
`text_year_published` int(10) NOT NULL,
`total_no_parts` int(10) NOT NULL,
`word_count_flag_con` int(11) NOT NULL DEFAULT '0',
`word_count_flag_med` int(11) NOT NULL DEFAULT '0',
`word_count_flag_time` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id_text_meta_data`,`text_language_id_text_language`,`text_time_period_id_text_time_period`,`text_topic_id_text_topic`,`text_topic_text_domain_id_text_domain`,`text_topic_text_medium_id_text_medium`,`region_country_id_region_country`,`text_authors_id_text_authors`),
KEY `IX2` (`text_time_period_id_text_time_period`),
KEY `IX4` (`region_country_id_region_country`),
KEY `FK_text_meta_data_text_authors` (`text_authors_id_text_authors`),
KEY `text_title` (`text_title`),
KEY `text_year_published` (`text_year_published`),
KEY `total_no_parts` (`total_no_parts`),
KEY `FK_text_language_id_text_language` (`text_language_id_text_language`),
KEY `FK_text_topic_id_text_topic` (`text_topic_id_text_topic`),
CONSTRAINT `FK_text_language_id_text_language` FOREIGN KEY (`text_language_id_text_language`) REFERENCES `text_language` (`id_text_language`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_text_meta_data_region_country` FOREIGN KEY (`region_country_id_region_country`) REFERENCES `region_country` (`id_region_country`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_text_meta_data_text_authors` FOREIGN KEY (`text_authors_id_text_authors`) REFERENCES `text_authors` (`id_text_authors`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_text_meta_data_text_time_period` FOREIGN KEY (`text_time_period_id_text_time_period`) REFERENCES `text_time_period` (`id_text_time_period`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_text_topic_id_text_topic` FOREIGN KEY (`text_topic_id_text_topic`) REFERENCES `text_topic` (`id_text_topic`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1013660 DEFAULT CHARSET=utf8