Left Join between two large tables too slow!!
We have 2 large tables having below structure:
CREATE TABLE Table1 (
`proid` bigint(20) NOT NULL,
`total` bigint(20) NOT NULL DEFAULT '0',
`create_date_time` datetime NOT NULL,
`site` varchar(255) DEFAULT NULL,
`exp` varchar(255) DEFAULT NULL,
KEY `pr_id` (`proid`),
KEY `pr_created_at` (`create_date_time`),
KEY `pr_site` (`site`),
KEY `pr_exp` (`exp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE Table2 (
`proid` bigint(20) NOT NULL,
`total` bigint(20) NOT NULL DEFAULT '0',
`create_date_time` datetime NOT NULL,
`site` varchar(255) DEFAULT NULL,
`exp` varchar(255) DEFAULT NULL,
KEY `pr_id` (`proid`),
KEY `pr_created_at` (`create_date_time`),
KEY `pr_siter` (`site`) ,
KEY `pr_exp` (`exp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Both the above tables have 18000+ records.
We're trying to fetch the data from the above two tables uing left join as below:
select
*
from
(select create_date_time,site,exp, sum(total) as imp_total
from Table2
group by create_date_time,site,exp) as imp
inner join
(select create_date_time,site,exp, sum(total) as req_total
from Table1
group by create_date_time,site,exp) as pr
on
imp.site=pr.site
and imp.create_date_time = pr.create_date_time
and imp.exp=pr.exp;
The requirement is that we need to group the data in tables by all three columns create_date_time,site and exp. Also there has to be a left join to fetch data from both tables.