Sorry for the long post, but the tables involved are quite big.
When I run the query bellow it normally takes about 1m to run. However, when I remove the correlated sub-query I get the query down to 15 seconds. So I think that’s the actual problem.
Problem is, I don’t really know how to get the SUM results without the correlated query.
QUERY I’m trying to run
SELECT cl_clients.vat as association_vat, cl_clients.name as association_name, cl_clients_with_regions.city_id, cl_clients_with_regions.nut_1, cl_clients_with_regions.nut_2, cl_clients_with_regions.nut_3,
company.vat as company_vat, company.name, company.section, company.division, company.cae,
SUM((
SELECT SUM((COALESCE((cl_finances.sales_community_market), 0) + COALESCE((cl_finances.sales_extra_market), 0)))
from cl_finances
where cl_finances.vat = company.vat
and cl_finances.year = 2018
)) as total_sum
FROM `cl_clients_with_regions`
JOIN `cl_client_intervention_areas` ON `cl_client_intervention_areas`.`city_id` = `cl_clients_with_regions`.`city_id`
JOIN `cl_clients` ON `cl_clients`.`vat` = `cl_client_intervention_areas`.`client_vat`
INNER JOIN cl_clients_with_regions company ON cl_clients_with_regions.vat = company.vat
WHERE `cl_clients_with_regions`.`encrypted_vat` IS NOT NULL
AND `cl_clients_with_regions`.`country` IS NOT NULL
AND `cl_clients_with_regions`.`cae` IS NOT NULL
AND `cl_clients_with_regions`.`cae` != ''
AND `cl_clients_with_regions`.`division` IS NOT NULL
AND `cl_clients_with_regions`.`section` = 'A'
AND `cl_clients_with_regions`.`nut_2` = 'Centro'
GROUP BY association_vat;
CL Clients create table syntax
create table cl_clients
(
vat varchar(20) default '' not null,
encrypted_vat varchar(32) null,
temporary_vat enum ('0', '1', '') default '0' null,
sig_id int null,
phc_id int null,
client_manager int null,
name varchar(300) null,
brand varchar(255) null,
`group` varchar(200) null,
class_internacional varchar(45) null,
logoimage varchar(400) null,
social_capital varchar(100) null,
address varchar(300) null,
gps varchar(25) null,
parish varchar(70) null,
zip_code varchar(10) null,
city int null,
district int null,
country int default 75 not null,
headquarter int null,
person_title varchar(7) null,
person_contact varchar(350) null,
person_phone varchar(20) null,
person_cell varchar(15) null,
person_email varchar(150) null,
person_function varchar(150) null,
language varchar(10) null,
phone varchar(20) null,
mobile_phone varchar(20) null,
fax varchar(20) null,
email varchar(50) null,
email_alternative varchar(50) null,
website varchar(300) null,
pme varchar(500) null,
pme_lider varchar(500) null,
cae varchar(15) null,
cae_2 varchar(15) null,
certified enum ('1', '0', '') null,
sector varchar(350) null,
workers int null,
foundation_date date null,
size enum ('Micro Empresa', 'Pequena empresa', 'Média empresa', 'Grande empresa', 'Não aplicável', 'PME', '') default '' null,
company_type varchar(455) default 'Empresa' null,
type enum ('novo', 'antigo', 'analizar', '') default 'analizar' null,
entity enum ('client', 'prospect', 'suplier', 'potential', '') default 'prospect' null,
client_to_country int null,
partner enum ('1', '0', '') default '0' not null,
partner_dp enum ('0', '1', '') default '0' null,
lucrative enum ('1', '0', '') default '1' not null,
dun varchar(100) null,
debt enum ('50k', '100k', '500k', '1m', '5m', '+5m', '') null,
bankruptcy enum ('1', '0', '') default '0' null,
competitors enum ('1', '0', '') default '0' null,
dun_date date null,
bank_1 varchar(30) null,
bank_2 varchar(30) null,
bank_3 varchar(30) null,
status enum ('0', '1', '') default '1' null,
followup enum ('0', '1', '') default '1' null,
classification enum ('Prestige', 'Premium', 'Current', '') default 'Current' null,
associated enum ('0', '1', '') default '0' null,
industry_id int null,
valid enum ('0', '1', '') default '1' null,
imported_at timestamp null,
modified_by int default 0 null,
responsible_id int null,
partner_type int null,
iberinform_id int null,
iberinform_date timestamp null,
legal_form text null,
going_concern text null,
score int null,
sector_score int null,
tax_status text null,
irc_debtors_ledger enum ('0', '1') null,
ss_debtors_ledger enum ('0', '1') null,
association_class_id int null,
nature enum ('business', 'commercial', 'sectorial', 'other') null,
geographic_scope enum ('national', 'regional', 'local') null,
intervention_nut enum ('nut_1', 'nut_2', 'nut_3') null,
ch_is_associated enum ('0', '1') null,
bi enum ('0', '1') default '0' null,
updated_at timestamp null,
constraint encrypted_vat_UNIQUE
unique (encrypted_vat),
constraint vat
unique (vat),
constraint cl_clients_cl_caes_cae_fk
foreign key (cae) references cl_caes (cae)
on update cascade,
constraint clients_industry_id_fk
foreign key (industry_id) references cl_industries (id),
constraint cltns_asso_clss_id_fk
foreign key (association_class_id) references cl_association_classifications (id)
on update cascade on delete set null,
constraint country_fk_id
foreign key (country) references cl_countries (id)
on update cascade on delete cascade
);
create index cl_client_name_idx
on cl_clients (name);
create index cl_clients_bi_idx
on cl_clients (bi);
create index cl_clients_brand_idx
on cl_clients (brand);
create index cl_clients_company_type_idx
on cl_clients (company_type);
create index cl_clients_geo_index
on cl_clients (city, district);
create index cl_clients_geographic_scope_idx
on cl_clients (geographic_scope);
create index cl_clients_group_idx
on cl_clients (`group`);
create index cl_clients_nature_idx
on cl_clients (nature);
create index cltns_asso_clss_id_fk_idx
on cl_clients (association_class_id);
create index country_idx
on cl_clients (country);
create index fk_clients_industries1_idx
on cl_clients (industry_id);
alter table cl_clients
add primary key (vat);
VIEW being used in the query
create view cl_clients_with_regions as
select `grupoch`.`cl_clients`.`vat` AS `vat`,
`grupoch`.`cl_clients`.`encrypted_vat` AS `encrypted_vat`,
`grupoch`.`cl_clients`.`name` AS `name`,
`grupoch`.`cl_clients`.`brand` AS `brand`,
`grupoch`.`cl_clients`.`country` AS `country`,
`grupoch`.`cl_clients`.`district` AS `district`,
`grupoch`.`cl_clients`.`city` AS `city`,
`grupoch`.`cl_cities`.`id` AS `city_id`,
`grupoch`.`cl_cities`.`name` AS `city_name`,
`grupoch`.`cl_cities`.`nut_1` AS `nut_1`,
`grupoch`.`cl_cities`.`nut_2` AS `nut_2`,
`grupoch`.`cl_cities`.`nut_3` AS `nut_3`,
`grupoch`.`cl_clients`.`cae` AS `cae`,
`grupoch`.`cl_caes`.`description` AS `cae_designation`,
`grupoch`.`cl_caes_divisions`.`division` AS `division`,
`grupoch`.`cl_caes_divisions`.`division_designation` AS `division_designation`,
`grupoch`.`cl_caes_divisions`.`section` AS `section`,
`grupoch`.`cl_caes_divisions`.`section_designation` AS `section_designation`
from (((`grupoch`.`cl_clients` join `grupoch`.`cl_cities` on ((
(`grupoch`.`cl_clients`.`city` = `grupoch`.`cl_cities`.`city_cod`) and (`grupoch`.`cl_clients`.`district` =
`grupoch`.`cl_cities`.`district_id`)))) join `grupoch`.`cl_caes` on ((`grupoch`.`cl_caes`.`cae` = `grupoch`.`cl_clients`.`cae`)))
join `grupoch`.`cl_caes_divisions`
on ((`grupoch`.`cl_caes_divisions`.`division` = `grupoch`.`cl_caes`.`division_id`)))
where ((`grupoch`.`cl_clients`.`country` in (75, 185, 186)) and (`grupoch`.`cl_clients`.`vat` is not null));
CL finances create table syntax
create table cl_finances
(
vat varchar(20) not null,
year int not null,
workers int null,
sells varchar(300) null,
sells_variation varchar(300) null,
international_sells varchar(300) null,
international_sells_variation varchar(300) null,
liquid_results varchar(300) null,
liquid_results_variation varchar(300) null,
capital varchar(300) null,
active_liquid varchar(300) null,
financial_autonomy varchar(300) null,
modified_by int default 0 null,
sales_profitability decimal(13, 2) null,
return_on_capital decimal(13, 2) null,
sales_community_market decimal(13, 2) null,
sales_extra_market decimal(13, 2) null,
created_at timestamp null,
updated_at timestamp null,
primary key (vat, year),
constraint clients_finances_vat
foreign key (vat) references cl_clients (vat)
on update cascade on delete cascade
);
create index cl_finances_year_idx
on cl_finances (year);
create index vat
on cl_finances (vat);
## Create table syntax for cl_client_intervention_areas
create table cl_client_intervention_areas
(
client_vat varchar(20) not null,
city_id int not null,
constraint fk_cl_client_intervention_areas_cl_cities1
foreign key (city_id) references cl_cities (id)
on update cascade on delete cascade,
constraint fk_cl_client_intervention_areas_cl_clients1
foreign key (client_vat) references cl_clients (vat)
on update cascade on delete cascade
);
create index fk_cl_client_intervention_areas_cl_cities1_idx
on cl_client_intervention_areas (city_id);
create index fk_cl_client_intervention_areas_cl_clients1_idx
on cl_client_intervention_areas (client_vat);
The explain result from this query is quite big
Advertisement
Answer
I believe the subquery is being executed once per row in the group by clause. Could you try the following if it makes it any better.
Change i have done is to perform the correlated query as a inline-view and then join with the main table/view — company.vat
SELECT cl_clients.vat as association_vat, cl_clients.name as association_name, cl_clients_with_regions.city_id, cl_clients_with_regions.nut_1, cl_clients_with_regions.nut_2, cl_clients_with_regions.nut_3,
company.vat as company_vat, company.name, company.section, company.division, company.cae,
SUM(temp_val.sum_val) as total_sum
FROM `cl_clients_with_regions`
JOIN `cl_client_intervention_areas` ON `cl_client_intervention_areas`.`city_id` = `cl_clients_with_regions`.`city_id`
JOIN `cl_clients` ON `cl_clients`.`vat` = `cl_client_intervention_areas`.`client_vat`
INNER JOIN cl_clients_with_regions company ON cl_clients_with_regions.vat = company.vat
LEFT JOIN (SELECT cl_finances.vat
,SUM((COALESCE((cl_finances.sales_community_market), 0) + COALESCE((cl_finances.sales_extra_market), 0))) as sum_val
FROM cl_finances
WHERE 1=1
AND cl_finances.year = 2018
GROUP BY cl_finances.vat
)temp_val
ON company.vat=temp_val.vat
WHERE `cl_clients_with_regions`.`encrypted_vat` IS NOT NULL
AND `cl_clients_with_regions`.`country` IS NOT NULL
AND `cl_clients_with_regions`.`cae` IS NOT NULL
AND `cl_clients_with_regions`.`cae` != ''
AND `cl_clients_with_regions`.`division` IS NOT NULL
AND `cl_clients_with_regions`.`section` = 'A'
AND `cl_clients_with_regions`.`nut_2` = 'Centro'
GROUP BY association_vat;