not able to get rows using indexed join columns
Posted by:
Ra Nala
Date: May 23, 2017 02:13AM
Hi Sir,Madam,
I am trying to retrieve data based on two tables(w01 ,wu_02). it is hanging and not able to get count(*) results for 1 hr also, kindly help me on this.
I have provided both table structures , please have look into and suggest me to solve my issue.
My simple query: index is exist on joined columns: model_id,srvc_plcy_id
SELECT COUNT(*) FROM wu_01 w01 ,wu_02 w02 where w01.srvc_plcy_id=w02.srvc_plcy_id and w01.model_id=w02.model_id
Table Create Table
-------- --------------------------------------------
wu_01 CREATE TABLE `wu_01` (
`model_id` bigint(20) DEFAULT NULL,
`model` varchar(50) DEFAULT NULL,
`cat_code` varchar(100) DEFAULT NULL,
`cat_name` varchar(250) DEFAULT NULL,
`brand_name` varchar(250) DEFAULT NULL,
`manfctr_be_code` varchar(100) DEFAULT NULL,
`manfctr_be_name` varchar(250) DEFAULT NULL,
`srvc_plcy_id` bigint(20) DEFAULT NULL,
`plcy_code` varchar(100) DEFAULT NULL,
`plcy_name` varchar(250) DEFAULT NULL,
`plcy_type` varchar(50) DEFAULT NULL,
`plcy_type_name` varchar(250) DEFAULT NULL,
`srvc_plcy_term_id` bigint(20) DEFAULT NULL,
`term_code` varchar(100) DEFAULT NULL,
`term_name` varchar(250) DEFAULT NULL,
`term_type` varchar(50) DEFAULT NULL,
`term_type_name` varchar(250) DEFAULT NULL,
`start_date` datetime DEFAULT NULL,
`end_date` datetime DEFAULT NULL,
`mis` int(11) DEFAULT NULL,
`year_mis_start_date` int(11) DEFAULT NULL,
`month_mis_start_date` varchar(10) DEFAULT NULL,
`month_no_mis_start_date` int(11) DEFAULT NULL,
`dis1` int(11) DEFAULT NULL,
`term_count` int(11) DEFAULT NULL,
KEY `inx_wu_01_model_id` (`model_id`),
KEY `inx_wu_01_manfctr_be_code` (`manfctr_be_code`),
KEY `inx_wu_01_srvc_plcy_id` (`srvc_plcy_id`),
KEY `inx_wu_01_srvc_plcy_term_id` (`srvc_plcy_term_id`),
KEY `inx_wu_01_mis` (`mis`),
KEY `inx_wu_01_year_mis_sd` (`year_mis_start_date`),
KEY `inx_wu_01_month_no_mis_sd` (`month_no_mis_start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
second table
Table Create Table
-------- ----------------------------------------------
wu_02 CREATE TABLE `wu_02` (
`model_id` bigint(20) DEFAULT NULL,
`model` varchar(50) DEFAULT NULL,
`cat_code` varchar(100) DEFAULT NULL,
`cat_name` varchar(250) DEFAULT NULL,
`brand_name` varchar(250) DEFAULT NULL,
`manfctr_be_code` varchar(100) DEFAULT NULL,
`manfctr_be_name` varchar(250) DEFAULT NULL,
`srvc_plcy_id` bigint(20) DEFAULT NULL,
`plcy_code` varchar(100) DEFAULT NULL,
`plcy_name` varchar(250) DEFAULT NULL,
`plcy_type` varchar(50) DEFAULT NULL,
`plcy_type_name` varchar(250) DEFAULT NULL,
`srvc_plcy_term_id` bigint(20) DEFAULT NULL,
`term_code` varchar(100) DEFAULT NULL,
`term_name` varchar(250) DEFAULT NULL,
`term_type` varchar(50) DEFAULT NULL,
`term_type_name` varchar(250) DEFAULT NULL,
`start_date` datetime DEFAULT NULL,
`end_date` datetime DEFAULT NULL,
`mis` int(11) DEFAULT NULL,
`year_mis_end_date` int(11) DEFAULT NULL,
`month_mis_end_date` varchar(10) DEFAULT NULL,
`month_no_mis_end_date` int(11) DEFAULT NULL,
`dis2` int(11) DEFAULT NULL,
`term_count` int(11) DEFAULT NULL,
KEY `inx_wu_02_model_id` (`model_id`),
KEY `inx_wu_02_manfctr_be_code` (`manfctr_be_code`),
KEY `inx_wu_02_srvc_plcy_id` (`srvc_plcy_id`),
KEY `inx_wu_02_srvc_plcy_term_id` (`srvc_plcy_term_id`),
KEY `inx_wu_02_mis` (`mis`),
KEY `inx_wu_02_year_mis_ed` (`year_mis_end_date`),
KEY `inx_wu_02_month_no_mis_ed` (`month_no_mis_end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Thanks,
Ra nala