How to optimize query with UNION?

A
abylalikkkk2022-04-19 09:36:57
MySQL
abylalikkkk, 2022-04-19 09:36:57
request
SELECT
      i_completion.id, i_completion.id_schet as main_tb_id, 'completion' as table_name, i_completion.number, i_completion.date, i_completion.summa_goods,
      i_invoice.id as id2, 'invoice' as table_name2, i_invoice.number as number2, i_invoice.date as date2, i_invoice.summa_goods as summa_goods2,
      NULL, NULL, NULL, NULL, NULL
      FROM i_schet_na_oplatu 
      INNER JOIN i_completion ON i_completion.id = i_schet_na_oplatu.id_completion
      LEFT JOIN i_invoice ON i_invoice.id = i_completion.id_invoice
      WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_completion.id = i_schet_na_oplatu.id_completion AND i_schet_na_oplatu.id_user = $user_id
    UNION ALL -- i_tmz on i_schet
      SELECT 
        i_tmz.id, i_tmz.id_schet as main_tb_id, 'tmz' as table_name, i_tmz.number, i_tmz.date, i_tmz.summa_goods, 
        i_invoice.id as id2, 'invoice' as table_name2, i_invoice.number as number2, i_invoice.date as date2, i_invoice.summa_goods as summa_goods2,
        i_cash_order.id as id3, 'cash' as table_name3, i_cash_order.number as number3, i_cash_order.date as date3, i_cash_order.summa_goods as summa_goods3
      FROM i_schet_na_oplatu
      INNER JOIN i_tmz ON i_tmz.id = i_schet_na_oplatu.id_tmz 
      LEFT JOIN i_invoice ON i_invoice.id = i_tmz.id_invoice 
      LEFT JOIN i_cash_order ON i_cash_order.id = i_tmz.id_cash
      WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_tmz.id = i_schet_na_oplatu.id_tmz AND i_schet_na_oplatu.id_user = $user_id
    UNION ALL -- i_invoice on i_schet
      SELECT 
        i_invoice.id, i_invoice.id_schet as main_tb_id, 'invoice' as table_name, i_invoice.number, i_invoice.date, i_invoice.summa_goods,
        NULL, NULL, NULL, NULL, NULL, 
        NULL, NULL, NULL, NULL, NULL
        FROM i_schet_na_oplatu
        INNER JOIN i_invoice ON i_invoice.id = i_schet_na_oplatu.id_invoice
        WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_invoice.id = i_schet_na_oplatu.id_invoice AND i_schet_na_oplatu.id_user = $user_id
    UNION ALL -- i_cash on i_schet
    SELECT i_cash_order.id, i_cash_order.id_schet as main_tb_id, 'cash' as table_name, i_cash_order.number, i_cash_order.date, i_cash_order.summa_goods, 
        NULL, NULL, NULL, NULL, NULL,
        NULL, NULL, NULL, NULL, NULL FROM i_schet_na_oplatu
        INNER JOIN i_cash_order ON i_cash_order.id = i_schet_na_oplatu.id_cash
        WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_cash_order.id = i_schet_na_oplatu.id_cash AND i_schet_na_oplatu.id_user = $user_id
    UNION ALL -- i_payorder_in on i_schet
    SELECT i_payorder_in.id, i_payorder_in.id_schet as main_tb_id, 'payorder_in' as table_name, i_payorder_in.number, i_payorder_in.date, i_payorder_in.summa_goods, 
      NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL FROM i_schet_na_oplatu
      INNER JOIN i_payorder_in ON i_payorder_in.id = i_schet_na_oplatu.id_payorder_in
      WHERE i_schet_na_oplatu.id IN($main_doc_id) AND i_payorder_in.id = i_schet_na_oplatu.id_payorder_in AND i_schet_na_oplatu.id_user = $user_id
    UNION ALL -- i_invoice on i_completion
      SELECT 
        i_invoice.id, i_invoice.id_completion as main_tb_id, 'invoice' as table_name, i_invoice.number, i_invoice.date, i_invoice.summa_goods, 
        NULL, NULL, NULL, NULL, NULL, 
        NULL, NULL, NULL, NULL, NULL
        FROM i_completion 
        INNER JOIN i_invoice ON i_invoice.id = i_completion.id_invoice 
        WHERE i_completion.id IN($main_doc_id) AND i_invoice.id = i_completion.id_invoice AND i_completion.id_user = $user_id
    UNION ALL -- i_invoice on i_tmz
      SELECT 
        i_invoice.id, i_invoice.id_tmz as main_tb_id, 'invoice' as table_name, i_invoice.number, i_invoice.date, i_invoice.summa_goods,
        NULL, NULL, NULL, NULL, NULL, 
        NULL, NULL, NULL, NULL, NULL
        FROM i_tmz
        INNER JOIN i_invoice ON i_invoice.id = i_tmz.id_invoice
        WHERe i_tmz.id IN ($main_doc_id) AND i_invoice.id = i_tmz.id_invoice AND i_tmz.id_user = $user_id
    UNION ALL -- i_cash_order on i_tmz
      SELECT 
        i_cash_order.id, i_cash_order.id_tmz as main_tb_id, 'cash' as table_name, i_cash_order.number, i_cash_order.date, i_cash_order.summa_goods,
        NULL, NULL, NULL, NULL, NULL,
        NULL, NULL, NULL, NULL, NULL FROM i_tmz
        INNER JOIN i_cash_order ON i_cash_order.id = i_tmz.id_cash
        WHERE i_tmz.id IN($main_doc_id) AND i_cash_order.id_tmz = i_tmz.id_cash AND i_tmz.id_user = $user_id
    UNION ALL -- i_statement on i_account_cash
      SELECT 
        i_statement.id, i_statement.id_account as main_tb_id, 'statement' as table_name, i_statement.number, i_statement.date, i_statement.summa_goods,
        NULL, NULL, NULL, NULL, NULL,
        NULL, NULL, NULL, NULL, NULL FROM i_account_cash
        INNER JOIN i_statement ON i_account_cash.id = i_statement.id_account
        WHERE i_account_cash.id IN($main_doc_id) AND i_account_cash.id_statement = i_statement.id AND i_statement.id_user = $user_id