What are the derived tables in my explain statement

Question

How does one determine a derived table listed in the EXPLAIN results? I have derived tables that seem to not have any keys that can be used which I think means that some optimization is required.

Select statement:

EXPLAIN SELECT * 
FROM vw_environment_deployment_statuses v
  WHERE asset_id=47;

View SQL:

SELECT
  `ac`.`asset_id` AS `asset_id`,
  `es`.`environment_id` AS `environment_id`,
  `d1`.`deployed_date` AS `last_deployed_date`,
  `d1`.`revision` AS `last_deployed_revision`,
  `d2`.`deployed_date` AS `last_successful_deployed_date`,
  `d2`.`revision` AS `last_successful_deployed_revision`,
  `e`.`acronym` AS `environment`,
  `v3`.`count_is_failed` AS `last_deployed_is_failed`
FROM (((((((((`asset_configurations` `ac`
  JOIN `vw_deployments` `d1`
    ON ((`ac`.`id` = `d1`.`asset_configuration_id`)))
  JOIN `vw_deployments` `d2`
    ON ((`ac`.`id` = `d2`.`asset_configuration_id`)))
  JOIN `servers` `s`
    ON ((`ac`.`server_id` = `s`.`id`)))
  JOIN `environments_servers` `es`
    ON ((`s`.`id` = `es`.`server_id`)))
  JOIN `environments` `e`
    ON ((`es`.`environment_id` = `e`.`id`)))
  JOIN `vw_last_environment_deployment_statuses` `v1`
    ON (((`ac`.`asset_id` = `v1`.`asset_id`) AND (`es`.`environment_id` = `v1`.`environment_id`) AND (`d1`.`deployed_date` = `v1`.`deployed_date`))))
  JOIN `vw_last_successful_environment_deployment_statuses` `v2`
    ON (((`ac`.`asset_id` = `v2`.`asset_id`) AND (`es`.`environment_id` = `v2`.`environment_id`) AND (`d2`.`deployed_date` = `v2`.`deployed_date`))))
  JOIN `vw_environment_fail_count` `v3`
    ON (((`ac`.`asset_id` = `v3`.`asset_id`) AND (`v3`.`environment_id` = `e`.`id`))))
  JOIN `domains` `dom`
    ON ((`ac`.`domain_id` = `dom`.`id`)))

Explain statement:

1   PRIMARY ac  ref PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_domains_id,FK_asset_configurations_servers_id FK_asset_configurations_assets_id   4   const   15  
1   PRIMARY s   eq_ref  PRIMARY PRIMARY 4   aps_cmdb.ac.server_id   1   Using index
1   PRIMARY es  ref PRIMARY,FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4   aps_cmdb.ac.server_id   1   Using index
1   PRIMARY dom eq_ref  PRIMARY PRIMARY 4   aps_cmdb.ac.domain_id   1   Using index
1   PRIMARY dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.ac.id  5   
1   PRIMARY dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.dep.asset_configuration_id 5   Using where

I'm good up to here... the following derived tables I have no idea what they consist of.

1   PRIMARY <derived9>  ALL (null)  (null)  (null)  (null)  148 Using where; Using join buffer
1   PRIMARY <derived7>  ALL (null)  (null)  (null)  (null)  148 Using where; Using join buffer
1   PRIMARY <derived4>  ALL (null)  (null)  (null)  (null)  150 Using where; Using join buffer
1   PRIMARY e   eq_ref  PRIMARY PRIMARY 4   v3.environment_id   1   Using where
9   DERIVED <derived15> ALL (null)  (null)  (null)  (null)  195 Using temporary; Using filesort
9   DERIVED ac  eq_ref  PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_domains_id,FK_asset_configurations_servers_id PRIMARY 4   lsd.asset_configuration_id  1   Using where
9   DERIVED es  ref PRIMARY,FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4   aps_cmdb.ac.server_id   1   Using index
9   DERIVED d   eq_ref  PRIMARY PRIMARY 4   aps_cmdb.ac.domain_id   1   Using index
9   DERIVED Asset   eq_ref  PRIMARY PRIMARY 4   aps_cmdb.ac.asset_id    1   Using index
9   DERIVED e   eq_ref  PRIMARY PRIMARY 4   aps_cmdb.es.environment_id  1   Using index
9   DERIVED s   eq_ref  PRIMARY PRIMARY 4   aps_cmdb.es.server_id   1   Using where; Using index
9   DERIVED dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.ac.id  5   Using where
9   DERIVED dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.dep.asset_configuration_id 5   Using where
9   DERIVED <derived12> ALL (null)  (null)  (null)  (null)  197 Using where; Using join buffer
15  DERIVED ac  index   PRIMARY UK_asset_configurations 777 (null)  229 Using where; Using index; Using temporary; Using filesort
15  DERIVED dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.ac.id  5   Using where
12  DERIVED ac  index   PRIMARY UK_asset_configurations 777 (null)  229 Using where; Using index; Using temporary; Using filesort
12  DERIVED dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.ac.id  5   
7   DERIVED ac  ALL PRIMARY,FK_asset_configurations_servers_id  (null)  (null)  (null)  229 Using where; Using temporary; Using filesort
7   DERIVED es  ref FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4   aps_cmdb.ac.server_id   1   Using index
7   DERIVED s   eq_ref  PRIMARY PRIMARY 4   aps_cmdb.ac.server_id   1   Using index
7   DERIVED dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.ac.id  5   Using where
4   DERIVED ac  ALL PRIMARY,FK_asset_configurations_servers_id  (null)  (null)  (null)  229 Using where; Using temporary; Using filesort
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 s   eq_ref  PRIMARY PRIMARY 4   aps_cmdb.ac.server_id   1   Using index
4   DERIVED dep ref FK_deployments_asset_configurations_id  FK_deployments_asset_configurations_id  4   aps_cmdb.ac.id  5   

Answer 1

Holy nested subqueries and parantheticals. AH!

Derived tables are temporary tables that are created to make you query work. They can be explicitely stated like in:

SELECT
    foo.horse
FROM
    (SELECT horse from bar) as foo

Where foo is a derived table. These often turn into temp tables in the query's execution on the server. In your case they are not so explicit. This is probably due to the fact that you are querying against views with views in them, and lord only knows how deep it goes.

Derived tables are nice because they allow you to SELECT data from a table (or a view) before joining it to another table, view, or derived table. They have a down side though, they are not indexed. Joins on derived tables are more expensive since you lose control over indexing. If your data is small, or you are careful in your nested(nested(nested())) design, then everything will be fine.

Lastly, and unrelated, I believe your parantheticals are superfluous. I believe your query would be much more readable if you did away with them.