create temp table from join contained + 65 milion of records Mysql

Question

I am using Mysql I have two tables to which i am doing multiple selects during proc run, each of them is more tan 100 milion records, to speed up proc i want to do this join ones have it as temp table (contains 65+ milion of records), later use this table in procedure . to create it . iam using . next .

   create temporary table T_join (INDEX(tm_id))
    SELECT 
        t1.id,
        t2.tm_id,
        t1.code,
        t1.bincode,
        t1.AU,
        t1.TA,
        t2.fin_amount,
        t2.m_id
    FROM
        pm_customers t1
            INNER JOIN
       client_transactions_final t2 ON t1.id = t2.id

all straight forward the only problem to create this temp table proc spend more than 20 minutes, how to speed up it ?

Answer 1

For this query:

SELECT c.id, ct.tm_id, c.code, t1.bincode, c.AU, c.TA,
       ct.fin_amount, ct.m_id
FROM pm_customers c INNER JOIN
     client_transactions_final ct
     ON c.id = ct.id;

You probably want an index on client_transactions_final(id, tm_id, fin_amount, m_id). This is a covering index for the query, so this should improve performance.

I'm not convinced that you really need to create this temporary tables. With the right indexes, join's are usually pretty fast. Temporary tables make more sense when you have aggregations and more complex queries. I'm not saying it won't work, just that it is not the first solution that would come to mind.