As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec.
The table in question is deployments d4.
The SQL Query:
select
a.id as asset_id,
a.asset_group_id,
ac.id as asset_configuration_id,
ac.domain_id,
ac.server_id,
e.acronym as environment,
e.id as environment_id,
d.name as domain,
-- last deployment
d1.id as last_deployment_id,
d1.revision as last_revision,
d1.deployed_date as last_deployed_date,
d1.deployed_by as last_deployed_by,
d1.is_failed as last_is_failed,
-- last successful deployment
d2.id as last_successful_deployment_id,
d2.revision as last_successful_revision,
d2.deployed_date as last_successful_deployed_date,
d2.deployed_by as last_successful_deployed_by,
-- last successful environment deployment
d3.revision as last_successful_environment_revision,
d3.deployed_date as last_successful_environment_deployed_date,
-- last environment deployment
d4.revision as last_environment_revision,
d4.deployed_date as last_environment_date,
d4.is_failed as last_environment_is_failed
from
assets a
inner join asset_configurations ac on a.id=ac.asset_id
inner join domains d on ac.domain_id=d.id
inner join servers s on ac.server_id=s.id
inner join environments_servers es on s.id=es.server_id
inner join environments e on es.environment_id=e.id
-- last deployment
inner join deployments d1 on ac.id=d1.asset_configuration_id
inner join vw_last_deployment_statuses ld on d1.asset_configuration_id = ld.asset_configuration_id and d1.deployed_date=ld.deployed_date
-- last successful deployment
inner join deployments d2 on ac.id=d2.asset_configuration_id
inner join vw_last_successful_deployment_statuses lsd on d2.asset_configuration_id = lsd.asset_configuration_id and d2.deployed_date=lsd.deployed_date
-- last successful environment deployment
inner join deployments d3 on ac.id=d3.asset_configuration_id
inner join vw_last_successful_environment_deployment_statuses lsed on a.id=lsed.asset_id and e.id=lsed.environment_id and d3.deployed_date=lsed.deployed_date
-- last environment deployment
inner join deployments d4 on ac.id=d4.asset_configuration_id
inner join vw_last_environment_deployment_statuses led on a.id=led.asset_id and e.id=led.environment_id and d4.deployed_date=led.deployed_date
where ac.id = 169;
Update #1
Here is the explain WITHOUT fourth inner join:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,ac,const,"PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_domains_id,FK_asset_configurations_servers_id",PRIMARY,4,const,1,
1,PRIMARY,d,const,PRIMARY,PRIMARY,4,const,1,
1,PRIMARY,s,const,PRIMARY,PRIMARY,4,const,1,"Using index"
1,PRIMARY,a,const,PRIMARY,PRIMARY,4,const,1,
1,PRIMARY,es,ref,"PRIMARY,FK_environments_servers_servers_id2",FK_environments_servers_servers_id2,4,const,1,"Using index"
1,PRIMARY,e,eq_ref,PRIMARY,PRIMARY,4,aps_cmdb.es.environment_id,1,
1,PRIMARY,d1,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,const,55,
1,PRIMARY,d2,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,const,55,
1,PRIMARY,d3,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,const,55,
1,PRIMARY,<derived4>,ALL,NULL,NULL,NULL,NULL,171,"Using where; Using join buffer"
1,PRIMARY,<derived3>,ALL,NULL,NULL,NULL,NULL,227,"Using where; Using join buffer"
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,230,"Using where; Using join buffer"
4,DERIVED,a,index,PRIMARY,FK_assets_asset_package_deployment_methods_id,1,NULL,101,"Using index; Using temporary; Using filesort"
4,DERIVED,ac,ref,"PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_servers_id",FK_asset_configurations_assets_id,4,aps_cmdb.a.id,1,
4,DERIVED,s,eq_ref,PRIMARY,PRIMARY,4,aps_cmdb.ac.server_id,1,"Using index"
4,DERIVED,es,ref,FK_environments_servers_servers_id2,FK_environments_servers_servers_id2,4,aps_cmdb.ac.server_id,1,"Using index"
4,DERIVED,d,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,aps_cmdb.ac.id,42,"Using where"
3,DERIVED,deployments,index,NULL,FK_deployments_asset_configurations_id,4,NULL,9877,"Using where"
2,DERIVED,deployments,index,NULL,FK_deployments_asset_configurations_id,4,NULL,9877,
Here is the explain WITH fourth inner join:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ac const PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_domains_id,FK_asset_configurations_servers_id PRIMARY 4 const 1
1 PRIMARY d const PRIMARY PRIMARY 4 const 1
1 PRIMARY s const PRIMARY PRIMARY 4 const 1 "Using index"
1 PRIMARY a const PRIMARY PRIMARY 4 const 1
1 PRIMARY es ref PRIMARY,FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4 const 1 "Using index"
1 PRIMARY e eq_ref PRIMARY PRIMARY 4 aps_cmdb.es.environment_id 1
1 PRIMARY d1 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55
1 PRIMARY d2 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55
1 PRIMARY d3 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55
1 PRIMARY d4 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 171 "Using where; Using join buffer"
1 PRIMARY <derived5> ALL NULL NULL NULL NULL 174 "Using where; Using join buffer"
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 227 "Using where; Using join buffer"
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 230 "Using where; Using join buffer"
5 DERIVED a index PRIMARY FK_assets_asset_package_deployment_methods_id 1 NULL 101 "Using index; Using temporary; Using filesort"
5 DERIVED ac ref PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_servers_id FK_asset_configurations_assets_id 4 aps_cmdb.a.id 1
5 DERIVED s eq_ref PRIMARY PRIMARY 4 aps_cmdb.ac.server_id 1 "Using index"
5 DERIVED es ref FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4 aps_cmdb.ac.server_id 1 "Using index"
5 DERIVED d ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 aps_cmdb.ac.id 42
4 DERIVED a index PRIMARY FK_assets_asset_package_deployment_methods_id 1 NULL 101 "Using index; Using temporary; Using filesort"
4 DERIVED ac ref PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_servers_id FK_asset_configurations_assets_id 4 aps_cmdb.a.id 1
4 DERIVED s eq_ref PRIMARY PRIMARY 4 aps_cmdb.ac.server_id 1 "Using index"
4 DERIVED es ref FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4 aps_cmdb.ac.server_id 1 "Using index"
4 DERIVED d ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 aps_cmdb.ac.id 42 "Using where"
3 DERIVED deployments index NULL FK_deployments_asset_configurations_id 4 NULL 9877 "Using where"
2 DERIVED deployments index NULL FK_deployments_asset_configurations_id 4 NULL 9877
Update #2
Example of vw_last_deployment_status
select
`aps_cmdb`.`deployments`.`asset_configuration_id` AS `asset_configuration_id`,
max(`aps_cmdb`.`deployments`.`deployed_date`) AS `deployed_date`
from
`aps_cmdb`.`deployments`
group by `aps_cmdb`.`deployments`.`asset_configuration_id`
Update #3
Extracted all view select statements and bundled it all into one statement:
select
a.id as asset_id,
a.asset_group_id,
ac.id as asset_configuration_id,
ac.domain_id,
ac.server_id,
e.acronym as environment,
e.id as environment_id,
d.name as domain,
-- last deployment
d1.id as last_deployment_id,
d1.revision as last_revision,
d1.deployed_date as last_deployed_date,
d1.deployed_by as last_deployed_by,
d1.is_failed as last_is_failed,
-- last successful deployment
d2.id as last_successful_deployment_id,
d2.revision as last_successful_revision,
d2.deployed_date as last_successful_deployed_date,
d2.deployed_by as last_successful_deployed_by,
-- last successful environment deployment
d3.revision as last_successful_environment_revision,
d3.deployed_date as last_successful_environment_deployed_date
-- last environment deployment
-- d4.revision as last_environment_revision,
-- d4.deployed_date as last_environment_date,
-- d4.is_failed as last_environment_is_failed
from
assets a
inner join asset_configurations ac on a.id=ac.asset_id
inner join domains d on ac.domain_id=d.id
inner join servers s on ac.server_id=s.id
inner join environments_servers es on s.id=es.server_id
inner join environments e on es.environment_id=e.id
-- last deployment
inner join deployments d1 on ac.id=d1.asset_configuration_id
inner join (
select
`aps_cmdb`.`deployments`.`asset_configuration_id` AS `asset_configuration_id`,
max(`aps_cmdb`.`deployments`.`deployed_date`) AS `deployed_date`
from
`aps_cmdb`.`deployments`
group by `aps_cmdb`.`deployments`.`asset_configuration_id`) ld on d1.asset_configuration_id = ld.asset_configuration_id and d1.deployed_date=ld.deployed_date
-- last successful deployment
inner join deployments d2 on ac.id=d2.asset_configuration_id
inner join (
select
`aps_cmdb`.`deployments`.`asset_configuration_id` AS `asset_configuration_id`,
max(`aps_cmdb`.`deployments`.`deployed_date`) AS `deployed_date`
from
`aps_cmdb`.`deployments`
where
(`aps_cmdb`.`deployments`.`is_failed` = 0)
group by `aps_cmdb`.`deployments`.`asset_configuration_id`) lsd on d2.asset_configuration_id = lsd.asset_configuration_id and d2.deployed_date=lsd.deployed_date
-- last successful environment deployment
inner join deployments d3 on ac.id=d3.asset_configuration_id
inner join (
select
`a`.`id` AS `asset_id`,
`es`.`environment_id` AS `environment_id`,
max(`d`.`deployed_date`) AS `deployed_date`
from
((((`aps_cmdb`.`deployments` `d`
join `aps_cmdb`.`asset_configurations` `ac` ON ((`d`.`asset_configuration_id` = `ac`.`id`)))
join `aps_cmdb`.`assets` `a` ON ((`ac`.`asset_id` = `a`.`id`)))
join `aps_cmdb`.`servers` `s` ON ((`ac`.`server_id` = `s`.`id`)))
join `aps_cmdb`.`environments_servers` `es` ON ((`s`.`id` = `es`.`server_id`)))
where
(`d`.`is_failed` = 0)
group by `a`.`id` , `es`.`environment_id`) lsed on a.id=lsed.asset_id and e.id=lsed.environment_id and d3.deployed_date=lsed.deployed_date
-- last environment deployment
inner join deployments d4 on ac.id=d4.asset_configuration_id
inner join (
select
`a`.`id` AS `asset_id`,
`es`.`environment_id` AS `environment_id`,
max(`d`.`deployed_date`) AS `deployed_date`
from
((((`aps_cmdb`.`deployments` `d`
join `aps_cmdb`.`asset_configurations` `ac` ON ((`d`.`asset_configuration_id` = `ac`.`id`)))
join `aps_cmdb`.`assets` `a` ON ((`ac`.`asset_id` = `a`.`id`)))
join `aps_cmdb`.`servers` `s` ON ((`ac`.`server_id` = `s`.`id`)))
join `aps_cmdb`.`environments_servers` `es` ON ((`s`.`id` = `es`.`server_id`)))
where
(`d`.`is_failed` = 0)
group by `a`.`id` , `es`.`environment_id`
) led on a.id=led.asset_id and e.id=led.environment_id and d4.deployed_date=led.deployed_date
where ac.id = 169;
Update #4
Even with the extracted view SQL, the single statement takes 40 seconds with the fourth inner join.
I also created a deployment view from the deployment table where the single blob field was omitted. Still takes 40 seconds.
Update #5
Doing a bit more profiling…
Without inner join:
- Innodb_rows_read 188 548 The number of rows read from InnoDB tables.
- Innodb_data_read 19 349 504 The amount of data read so far, in bytes.
With inner join:
- Innodb_rows_read 19 207 716 The number of rows read from InnoDB tables.
- Innodb_data_read 2 147 205 120 The amount of data read so far, in bytes.
Best Answer
Try making a temp table with just the data you need and doing a join to that. For each self join do another temp table. I would start with one at a time and check the performance.