MySQL – Query Main Table Rows and All Children from it’s rows on the same query

MySQLperformancequery-performanceunion

I have the view_product which contains what i need and i have many child tables for it.
Currently i have a query where i get everything in a single query (unions) and programmatically split and organize the hierarchy on objects.

The main table have around 4k rows, which is nothing, but the query is taking 2s to run no matter the result size (which would be good).
My concern is that this 2s are increasing with the table length and if 4k rows are making it this slow than what 50k rows would do?

This is what i'm running currently:

(SELECT view_product.id 
 , view_product.id_pedido 
 , view_product.id_produto  view_product.agent_entity_id 
 , view_product.agent_employee_id 
 , view_product.id_workflow_production 
 , view_product.id_workflow_production_agent
 , '' as expand_Components_id
 , '' as expand_Components_id_pedido_produto
 , '' as expand_Components_id_produto_componente
 , '' as expand_Components_acao
 , '' as expand_Components_quantidade
 , '' as expand_Components_valor_acrescido
 , '' as expand_Components_exclusion_date
 , '' as expand_Components_excluido
 , '' as expand_Components_nome_produto
 , '' as expand_Components_preco
 , '' as expand_Components_unidade_medida_produto
 , '' as expand_Components_medida_uso
 , '' as expand_Components_multiplica_valor_produto
 , '' as expand_Components_unidade_medida_produto_compra
 , '' as expand_Components_unidade_medida_produto_venda
 , '' as expand_Image_id
 , '' as expand_Image_product_id
 , '' as expand_Image_url
 , '' as expand_Image_path
 , '' as expand_Image_main
 , '' as expand_Configurations_id
 , '' as expand_Configurations_id_produto
 , '' as expand_Configurations_id_categoria_produto
 , '' as expand_Configurations_id_configuracao_escolhida
 , '' as expand_Configurations_id_pedido_produto_configuracao_escolhida
 , '' as expand_Configurations_nome
 , '' as expand_Configurations_tipo
 , '' as expand_Configurations_padrao
 , '' as expand_Configurations_agrupador
 , '' as expand_Configurations_valor
 , '' as expand_Phase_id
 , '' as expand_Phase_id_workflow_production
 , '' as expand_Phase_id_empresa_grupo
 , '' as expand_Phase_order
 , '' as expand_Phase_name
 , '' as expand_Phase_description
 , '' as expand_Phase_finished
 , '' as expand_Phase_deletion_date
 , '' as expand_Agent_id
 , '' as expand_Agent_id_empresa_filial
 , '' as expand_Agent_id_pedido_produto
 , '' as expand_Agent_id_funcionario
 , '' as expand_Agent_id_workflow_production_phase
 , '' as expand_Agent_start_date
 , '' as expand_Agent_conclusion_date
 , '' as expand_Agent_rejection_date
 , '@self' as current_expanded_child
 FROM view_product 
 WHERE (view_product.exclusion_date IS null )
 AND (view_product.creation_date > '2018-07-05 00:00:00')
 AND (view_product.id_pedido = 2501 )
 ORDER BY  `payed` ASC , `nome_publico` ASC )



UNION ALL


(
SELECT view_product.id 
 , view_product.id_pedido 
 , view_product.id_produto  view_product.agent_entity_id 
 , view_product.agent_employee_id 
 , view_product.id_workflow_production 
 , view_product.id_workflow_production_agent
 , expand_Components.id as expand_Components_id
 , expand_Components.id_pedido_produto as expand_Components_id_pedido_produto
 , expand_Components.id_produto_componente as expand_Components_id_produto_componente
 , expand_Components.acao as expand_Components_acao
 , expand_Components.quantidade as expand_Components_quantidade
 , expand_Components.valor_acrescido as expand_Components_valor_acrescido
 , expand_Components.exclusion_date as expand_Components_exclusion_date
 , expand_Components.excluido as expand_Components_excluido
 , view_produtos_empresa.nome_publico as expand_Components_nome_produto
 , view_produtos_empresa.preco as expand_Components_preco
 , view_produtos_empresa.unidade_medida_produto as expand_Components_unidade_medida_produto
 , produto_componente.quantidade as expand_Components_medida_uso
 , view_produtos_empresa.multiplica_valor_produto as expand_Components_multiplica_valor_produto
 , view_produtos_empresa.unidade_medida_produto_compra as expand_Components_unidade_medida_produto_compra
 , view_produtos_empresa.unidade_medida_produto_venda as expand_Components_unidade_medida_produto_venda

 , '' as expand_Image_id
 , '' as expand_Image_product_id
 , '' as expand_Image_url
 , '' as expand_Image_path
 , '' as expand_Image_main
 , '' as expand_Configurations_id
 , '' as expand_Configurations_id_produto
 , '' as expand_Configurations_id_categoria_produto
 , '' as expand_Configurations_id_configuracao_escolhida
 , '' as expand_Configurations_id_pedido_produto_configuracao_escolhida
 , '' as expand_Configurations_nome
 , '' as expand_Configurations_tipo
 , '' as expand_Configurations_padrao
 , '' as expand_Configurations_agrupador
 , '' as expand_Configurations_valor
 , '' as expand_Phase_id
 , '' as expand_Phase_id_workflow_production
 , '' as expand_Phase_id_empresa_grupo
 , '' as expand_Phase_order
 , '' as expand_Phase_name
 , '' as expand_Phase_description
 , '' as expand_Phase_finished
 , '' as expand_Phase_deletion_date
 , '' as expand_Agent_id
 , '' as expand_Agent_id_empresa_filial
 , '' as expand_Agent_id_pedido_produto
 , '' as expand_Agent_id_funcionario
 , '' as expand_Agent_id_workflow_production_phase
 , '' as expand_Agent_start_date
 , '' as expand_Agent_conclusion_date
 , '' as expand_Agent_rejection_date
 , 'expand_Components' as current_expanded_child
 FROM view_product 

INNER JOIN pedido_produto_componente as expand_Components 
 ON (expand_Components.id_pedido_produto = view_product.id )

 LEFT JOIN pedido_produto ON  pedido_produto.id = view_product.id
 LEFT JOIN produto_componente ON  produto_componente.id_produto_componente = expand_Components.id_produto_componente AND  produto_componente.id_produto_agrupador = pedido_produto.id_produto
 LEFT JOIN view_produtos_empresa ON  view_produtos_empresa.id = expand_Components.id_produto_componente
 WHERE (view_product.exclusion_date IS null )
 AND (view_product.creation_date > '2018-07-05 00:00:00')
 AND (view_product.id_pedido = 2501 )
 ORDER BY  `payed` ASC , `nome_publico` ASC )


UNION ALL


(
SELECT view_product.id 
 , view_product.id_pedido 
 , view_product.id_produto  view_product.agent_entity_id 
 , view_product.agent_employee_id 
 , view_product.id_workflow_production 
 , view_product.id_workflow_production_agent
 , '' as expand_Components_id
 , '' as expand_Components_id_pedido_produto
 , '' as expand_Components_id_produto_componente
 , '' as expand_Components_acao
 , '' as expand_Components_quantidade
 , '' as expand_Components_valor_acrescido
 , '' as expand_Components_exclusion_date
 , '' as expand_Components_excluido
 , '' as expand_Components_nome_produto
 , '' as expand_Components_preco
 , '' as expand_Components_unidade_medida_produto
 , '' as expand_Components_medida_uso
 , '' as expand_Components_multiplica_valor_produto
 , '' as expand_Components_unidade_medida_produto_compra
 , '' as expand_Components_unidade_medida_produto_venda
 , expand_Image.id as expand_Image_id
 , expand_Image.product_id as expand_Image_product_id
 , expand_Image.url as expand_Image_url
 , expand_Image.path as expand_Image_path
 , expand_Image.main as expand_Image_main

 , '' as expand_Configurations_id
 , '' as expand_Configurations_id_produto
 , '' as expand_Configurations_id_categoria_produto
 , '' as expand_Configurations_id_configuracao_escolhida
 , '' as expand_Configurations_id_pedido_produto_configuracao_escolhida
 , '' as expand_Configurations_nome
 , '' as expand_Configurations_tipo
 , '' as expand_Configurations_padrao
 , '' as expand_Configurations_agrupador
 , '' as expand_Configurations_valor
 , '' as expand_Phase_id
 , '' as expand_Phase_id_workflow_production
 , '' as expand_Phase_id_empresa_grupo
 , '' as expand_Phase_order
 , '' as expand_Phase_name
 , '' as expand_Phase_description
 , '' as expand_Phase_finished
 , '' as expand_Phase_deletion_date
 , '' as expand_Agent_id
 , '' as expand_Agent_id_empresa_filial
 , '' as expand_Agent_id_pedido_produto
 , '' as expand_Agent_id_funcionario
 , '' as expand_Agent_id_workflow_production_phase
 , '' as expand_Agent_start_date
 , '' as expand_Agent_conclusion_date
 , '' as expand_Agent_rejection_date
 , 'expand_Image' as current_expanded_child
 FROM view_product 

INNER JOIN product_picture as expand_Image 
 ON (expand_Image.product_id = view_product.id_produto )

 WHERE (view_product.exclusion_date IS null )
 AND (view_product.creation_date > '2018-07-05 00:00:00')
 AND (view_product.id_pedido = 2501 )
 ORDER BY  `payed` ASC , `nome_publico` ASC )


UNION ALL


(
SELECT view_product.id 
 , view_product.id_pedido 
 , view_product.id_produto  view_product.agent_entity_id 
 , view_product.agent_employee_id 
 , view_product.id_workflow_production 
 , view_product.id_workflow_production_agent
 , '' as expand_Components_id
 , '' as expand_Components_id_pedido_produto
 , '' as expand_Components_id_produto_componente
 , '' as expand_Components_acao
 , '' as expand_Components_quantidade
 , '' as expand_Components_valor_acrescido
 , '' as expand_Components_exclusion_date
 , '' as expand_Components_excluido
 , '' as expand_Components_nome_produto
 , '' as expand_Components_preco
 , '' as expand_Components_unidade_medida_produto
 , '' as expand_Components_medida_uso
 , '' as expand_Components_multiplica_valor_produto
 , '' as expand_Components_unidade_medida_produto_compra
 , '' as expand_Components_unidade_medida_produto_venda
 , '' as expand_Image_id
 , '' as expand_Image_product_id
 , '' as expand_Image_url
 , '' as expand_Image_path
 , '' as expand_Image_main
 , expand_Configurations.id as expand_Configurations_id
 , produto_categoria.id_produto as expand_Configurations_id_produto
 , expand_Configurations.id_categoria_produto as expand_Configurations_id_categoria_produto
 , pedido_produto_configuracao.id as expand_Configurations_id_configuracao_escolhida
 , pedido_produto_configuracao.id_pedido_produto as expand_Configurations_id_pedido_produto_configuracao_escolhida
 , expand_Configurations.nome as expand_Configurations_nome
 , expand_Configurations.tipo as expand_Configurations_tipo
 , expand_Configurations.padrao as expand_Configurations_padrao
 , expand_Configurations.agrupador as expand_Configurations_agrupador
 , (CASE 
                    WHEN pedido_produto_configuracao.id THEN pedido_produto_configuracao.valor
                    ELSE expand_Configurations.padrao
                END) as expand_Configurations_valor

 , '' as expand_Phase_id
 , '' as expand_Phase_id_workflow_production
 , '' as expand_Phase_id_empresa_grupo
 , '' as expand_Phase_order
 , '' as expand_Phase_name
 , '' as expand_Phase_description
 , '' as expand_Phase_finished
 , '' as expand_Phase_deletion_date
 , '' as expand_Agent_id
 , '' as expand_Agent_id_empresa_filial
 , '' as expand_Agent_id_pedido_produto
 , '' as expand_Agent_id_funcionario
 , '' as expand_Agent_id_workflow_production_phase
 , '' as expand_Agent_start_date
 , '' as expand_Agent_conclusion_date
 , '' as expand_Agent_rejection_date
 , 'expand_Configurations' as current_expanded_child
 FROM view_product 

 INNER JOIN produto_categoria ON  produto_categoria.id_produto = view_product.id_produto
INNER JOIN categoria_produto_configuracao as expand_Configurations 
 ON (produto_categoria.id_categoria_produto = expand_Configurations.id_categoria_produto )

 LEFT JOIN pedido_produto_configuracao ON  pedido_produto_configuracao.id_categoria_produto_configuracao = expand_Configurations.id AND  pedido_produto_configuracao.id_pedido_produto = view_product.id
 WHERE (view_product.exclusion_date IS null )
 AND (view_product.creation_date > '2018-07-05 00:00:00')
 AND (view_product.id_pedido = 2501 )
 ORDER BY  `payed` ASC , `nome_publico` ASC )


UNION ALL


(
SELECT view_product.id 
 , view_product.id_pedido 
 , view_product.id_produto  view_product.agent_entity_id 
 , view_product.agent_employee_id 
 , view_product.id_workflow_production 
 , view_product.id_workflow_production_agent
 , '' as expand_Components_id
 , '' as expand_Components_id_pedido_produto
 , '' as expand_Components_id_produto_componente
 , '' as expand_Components_acao
 , '' as expand_Components_quantidade
 , '' as expand_Components_valor_acrescido
 , '' as expand_Components_exclusion_date
 , '' as expand_Components_excluido
 , '' as expand_Components_nome_produto
 , '' as expand_Components_preco
 , '' as expand_Components_unidade_medida_produto
 , '' as expand_Components_medida_uso
 , '' as expand_Components_multiplica_valor_produto
 , '' as expand_Components_unidade_medida_produto_compra
 , '' as expand_Components_unidade_medida_produto_venda
 , '' as expand_Image_id
 , '' as expand_Image_product_id
 , '' as expand_Image_url
 , '' as expand_Image_path
 , '' as expand_Image_main
 , '' as expand_Configurations_id
 , '' as expand_Configurations_id_produto
 , '' as expand_Configurations_id_categoria_produto
 , '' as expand_Configurations_id_configuracao_escolhida
 , '' as expand_Configurations_id_pedido_produto_configuracao_escolhida
 , '' as expand_Configurations_nome
 , '' as expand_Configurations_tipo
 , '' as expand_Configurations_padrao
 , '' as expand_Configurations_agrupador
 , '' as expand_Configurations_valor
 , expand_Phase.id as expand_Phase_id
 , expand_Phase.id_workflow_production as expand_Phase_id_workflow_production
 , expand_Phase.id_empresa_grupo as expand_Phase_id_empresa_grupo
 , expand_Phase.order as expand_Phase_order
 , expand_Phase.name as expand_Phase_name
 , expand_Phase.description as expand_Phase_description
 , expand_Phase.finished as expand_Phase_finished
 , expand_Phase.deletion_date as expand_Phase_deletion_date

 , '' as expand_Agent_id
 , '' as expand_Agent_id_empresa_filial
 , '' as expand_Agent_id_pedido_produto
 , '' as expand_Agent_id_funcionario
 , '' as expand_Agent_id_workflow_production_phase
 , '' as expand_Agent_start_date
 , '' as expand_Agent_conclusion_date
 , '' as expand_Agent_rejection_date
 , 'expand_Phase' as current_expanded_child
 FROM view_product 

 INNER JOIN workflow_production_agent ON  workflow_production_agent.id = id_workflow_production_agent
INNER JOIN workflow_production_phase as expand_Phase 
 ON (expand_Phase.id = workflow_production_agent.id_workflow_production_phase )

 WHERE (view_product.exclusion_date IS null )
 AND (view_product.creation_date > '2018-07-05 00:00:00')
 AND (view_product.id_pedido = 2501 )
 ORDER BY  `payed` ASC , `nome_publico` ASC )


UNION ALL


(
SELECT view_product.id 
 , view_product.id_pedido 
 , view_product.id_produto  view_product.agent_entity_id 
 , view_product.agent_employee_id 
 , view_product.id_workflow_production 
 , view_product.id_workflow_production_agent
 , '' as expand_Components_id
 , '' as expand_Components_id_pedido_produto
 , '' as expand_Components_id_produto_componente
 , '' as expand_Components_acao
 , '' as expand_Components_quantidade
 , '' as expand_Components_valor_acrescido
 , '' as expand_Components_exclusion_date
 , '' as expand_Components_excluido
 , '' as expand_Components_nome_produto
 , '' as expand_Components_preco
 , '' as expand_Components_unidade_medida_produto
 , '' as expand_Components_medida_uso
 , '' as expand_Components_multiplica_valor_produto
 , '' as expand_Components_unidade_medida_produto_compra
 , '' as expand_Components_unidade_medida_produto_venda
 , '' as expand_Image_id
 , '' as expand_Image_product_id
 , '' as expand_Image_url
 , '' as expand_Image_path
 , '' as expand_Image_main
 , '' as expand_Configurations_id
 , '' as expand_Configurations_id_produto
 , '' as expand_Configurations_id_categoria_produto
 , '' as expand_Configurations_id_configuracao_escolhida
 , '' as expand_Configurations_id_pedido_produto_configuracao_escolhida
 , '' as expand_Configurations_nome
 , '' as expand_Configurations_tipo
 , '' as expand_Configurations_padrao
 , '' as expand_Configurations_agrupador
 , '' as expand_Configurations_valor
 , '' as expand_Phase_id
 , '' as expand_Phase_id_workflow_production
 , '' as expand_Phase_id_empresa_grupo
 , '' as expand_Phase_order
 , '' as expand_Phase_name
 , '' as expand_Phase_description
 , '' as expand_Phase_finished
 , '' as expand_Phase_deletion_date
 , expand_Agent.id as expand_Agent_id
 , expand_Agent.id_empresa_filial as expand_Agent_id_empresa_filial
 , expand_Agent.id_pedido_produto as expand_Agent_id_pedido_produto
 , expand_Agent.id_funcionario as expand_Agent_id_funcionario
 , expand_Agent.id_workflow_production_phase as expand_Agent_id_workflow_production_phase
 , expand_Agent.start_date as expand_Agent_start_date
 , expand_Agent.conclusion_date as expand_Agent_conclusion_date
 , expand_Agent.rejection_date as expand_Agent_rejection_date

 , 'expand_Agent' as current_expanded_child
 FROM view_product 

INNER JOIN workflow_production_agent as expand_Agent 
 ON (expand_Agent.id = view_product.id_workflow_production_agent )

 WHERE (view_product.exclusion_date IS null )
 AND (view_product.creation_date > '2018-07-05 00:00:00')
 AND (view_product.id_pedido = 2501 )
 ORDER BY  `payed` ASC , `nome_publico` ASC )

Best Answer

Time each of the SELECTs to see which is slowest, then let's discuss that one.

Please provide SHOW CREATE VIEW view_product and SHOW CREATE TABLE for the tables used in it.

If it weren't a VIEW, I will quickly say that it needs

INDEX(id_pedido, exclusion_date, creation_date)

Then I would complain that I can't tell where these two columns come from:

ORDER BY  `payed` ASC , `nome_publico` ASC

But then I would point out that the ORDER BY is useless because it is attached to each SELECT, and the UNION ALL is free to ignore the ordering of each component. Perhaps you want the ORDER BY only at the very end?

Related Question