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