Mysql – How to improve performance of a query that joins three tables

MySQLperformancePHP

I want to fetch data from three tables: table A, table B and table C.

  1. Table A in databaseA has the customerid column which is a primary key
  2. Tables B and C in databaseB also have same column customerid (not primary key).

I want to retrieve the last transaction date in table A and table B for each customer in table A.

Expected result should have these columns:

  1. customerID
  2. Max(date in table B)
  3. Max(date in table C)

The query below takes forever to run. How do I optimize the query to get desired result in less than 5 sec?

NB: tableA and tableB have about 10 million data each and both databases have the same character set.

select
   a.customerid, 
   a.custname as name,   
   max(b.lastdate) as lastdt,
   max(c.lastdate) as lastdtc,

     case 
        when  max(b.lastdate) < date_sub(curdate(), interval 7 day) 
          and max(c.lastdate) > date_sub(curdate(), interval 7 day)
                  then 'INACTIVE'
        when  max(c.lastdate) < date_sub(curdate(), interval 7 day) 
         and  max(b.lastdate) < date_sub(curdate(), interval 7 day) 
                  then 'DORMANT'
        when  max(c.lastdate) is null 
         and  max(b.lastdate) < date_sub(curdate(), interval 7 day)
                  then 'DORMANT'
        else 
                       'ACTIVE'
        end as Status
  from 
           database1.table a
     inner join 
           database2.table b
           on a.customerid=b.customerid
        left join 
          database2.table c
           on a.customerid=c.customerid
           where a.customername like concat('$fromclient','%')
    group by a.customerid
         order by lastdt
    limit $fromclient offset $fromclient

Best Answer

Step 1: Build a query that computes lastdt, lastdtc, and status.

Step 2: Use that as a "derived" table to join to the other table(s) for the rest of the info.

The intent of Step 1 is to focus on the complexity of the GROUP BY and yield a much smaller table. Then Step 2 has less work to do.

Related Question