long query takes a long time I have a rather complex query in mysql 5.1.73-community. It involves joining tables and view at the same time.

Here is the query:

VIEW v_summary AS (
select (--some 35 columns fro tabel A B C D F)
from (((((ht_counters_sales_items_b a
       left join ht_counters_sales_b b
         on ((a.tcsi_tcs_id = b.tcs_id)))
      left join ht_counters_trans_b c
        on ((b.tcs_no = c.tct_ref_no)))
     left join **VIEW_counters_activities** d
       on ((c.tct_tca_id = d.tca_id)))
    left join m_items e
      on ((e.mi_id = a.tcsi_mi_id)))
   left join m_items_categories f
     on ((f.mic_id = e.mi_mic_id)))
where ((c.tct_voided = '0')
       and (a.tcsi_voided = '0'))
group by a.tcsi_id)$$


Table A : index (tcsi_id) PRIMARY

Table B : index (tcs_id) PRIMARY

Table C : index (tct_id) PRIMARY

Table E : index (mi_id) PRIMARY, (mi_code,mi_id),(mi_name,mi_id),
(mi_mic_id,mi_id),(mi_mu_id,mi_id),(mi_mu_id,mi_mic_id,mi_id),(mi_id,mi_mu_id,mi_mic_id)

Tabel F : index (mic_id) PRIMARY, (mic_mu_id),(mic_type),(mic_lft,mic_rgt,miclvl),(mic_name,mic_id)

Another view that is involved in the above view is as follows : 

VIEW **VIEW_counters_activities** AS(SELECT
  SUM((CASE g.tct_ref_journal WHEN 'CREDIT' THEN (g.tct_ref_value - g.tct_ref_voided) WHEN 'DEBIT' THEN ((0 - g.tct_ref_value) + g.tct_ref_voided) ELSE 0 END)) AS tca_close_cash,
  a.tca_id            AS tca_id,
  a.tca_open_date     AS tca_date,
  a.tca_open_date     AS tca_open_date,
  a.tca_open_date     AS tca_open_time,
  a.tca_close_date    AS tca_close_date,
  a.tca_close_date    AS tca_close_time,
  a.tca_mc_id         AS tca_mc_id,
  a.tca_mst_id        AS tca_mst_id,
  a.tca_state         AS tca_state,
  a.tca_user_id       AS tca_user_id,
  a.tca_open_cash     AS tca_open_cash,
  a.tca_mutation_cash AS tca_mutation_cash,
  a.tca_controller    AS tca_controller,
  a.tca_coordinator   AS tca_coordinator,
  b.mhr_scd_name      AS tca_mhr_scd_name,
  c.fullname          AS tca_user_name,
  d.fullname          AS tca_controller_name,
  e.fullname          AS tca_coordinator_name,
  f.mc_name           AS tca_mc_name,
  f.mc_ip_address     AS tca_mc_ip_address,
  f.mc_mcp_type       AS tca_mc_mcp_type,
  f.mc_mcp_name       AS tca_mc_mcp_name,
  f.mc_ml_id          AS tca_mc_ml_id,
  f.mc_ml_type        AS tca_mc_ml_type,
  f.mc_ml_name        AS tca_mc_ml_name,
  h.mpr_name          AS tca_receipt_printer_name,
  h.mpr_ip_address    AS tca_receipt_printer_address,
  i.mpr_name          AS tca_report_printer_name,
  i.mpr_ip_address    AS tca_report_printer_address,
  j.mst_name          AS tca_mst_name
FROM (((((((((ht_counters_activities_b a
           LEFT JOIN m_hr_schedules b
             ON ((a.tca_mhr_scd_id = b.mhr_scd_id)))
          LEFT JOIN v_users c
            ON ((a.tca_user_id = c.id)))
         LEFT JOIN v_users d
           ON ((a.tca_controller = d.id)))
        LEFT JOIN v_users e
          ON ((a.tca_coordinator = e.id)))
       LEFT JOIN v_counters f
         ON ((a.tca_mc_id = f.mc_id)))
      LEFT JOIN ht_counters_trans_b g
        ON (((a.tca_id = g.tct_tca_id)
             AND (g.tct_ref_mpm_type = 'CASH')
             AND (g.tct_voided = 0))))
     LEFT JOIN m_printers h
       ON ((f.mc_receipt_printer = h.mpr_id)))
    LEFT JOIN m_printers i
      ON ((f.mc_report_printer = i.mpr_id)))
   LEFT JOIN m_sales_types j
     ON ((a.tca_mst_id = j.mst_id)))
GROUP BY a.tca_id))


As i am writing this, the query SELECT * FROM v_summary WHERE MONTH(trx_date) = 7 has run for 70 minutes and still counting.

How can i make it faster ?

All of the tables are INNODB.

MySQL settings:

bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 2147483648
innodb_log_buffer_size  16777216
join_buffer_size    131072
key_buffer_size 57671680
myisam_sort_buffer_size 130023424
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    65536
read_rnd_buffer_size    262144
sort_buffer_size    262144
sql_buffer_result   OFF
max_heap_table_size 64M
tmp_table_size 124M
SET PROFILING = ON
Several optimizations tips:


Left joins to inner joins

You can probably simplify the views a little bit by converting some LEFT JOINs into INNER JOINs. For instance, for the first view, you have a WHERE clause of c.tct_voided = 0, this will make the involved join behave like an inner join instead of a left one. If the row on c were not found, the c.tct_voided = 0 won't be true, and won't be returned. Explicitly changing the LEFT to INNER will help the database some. That might improve a little bit the execution time. 

Some of the other LEFT JOINs might already by INNER JOINs just because of referential integrity, or the nature of your data. If you know for sure that there's always a corresponding row on your right table, change the LEFT to INNER. 
Indexes on the expressions that are part of a join

If you have a join condition that is

(ht_counters_sales_items_b a
LEFT JOIN ht_counters_sales_b b
    ON a.tcsi_tcs_id = b.tcs_id)
LEFT JOIN ht_counters_trans_b c
    ON b.tcs_no = c.tct_ref_no


You are likely to gain a lot of speed if there is a KEY(tct_ref_no) on table ht_counters_trans_b. The same might apply to KEY(tcs_no) on table ht_counters_sales_b (if the LEFT JOIN were an INNER JOIN and MySQL would decide to process it right-to-left, instead of left-to-right).
Not forcing order of join execution

In some occasions (and this really needs to be tested, I don't know how good is MySQL at it), not putting any (...) on the JOINs gives the database more freedom to process them in the order it seems more appropriate. This is normally true for INNER JOINs, but shouldn't have a significant effect on LEFT JOINs.
WHERE clauses

SELECT * FROM v_summary WHERE MONTH(trx_date) = 7


If you want to look for all the data for July this year, you're more likely to get a much faster result if trx_date is indexed, and you write your query like:

SELECT * FROM v_summary WHERE trx_date BETWEEN '2017-07-01' and '2017-07-31'


This will allow the database to use the index. Having a function applied to the column will prevent it from using it, and force a sequential scan on all the results. And that's not what you want.