0

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
3
  • 1
    please add tables definition (with current indexes)
    – a_vlad
    Nov 15 '16 at 20:28
  • Can you provide an execution plan? Offhand the where a.customername like concat('$fromclient','%') looks potentially problematic. A like on a string in a largerish table isn't typically the most performant thing. Nov 15 '16 at 20:56
  • left like is not a issue, but need understand - it indexed or not? other what help to understand performance problems - SHOW INDEX FROM tableA (same for table B) - in additional to table schema it show potential indexes cardinality and allow +- understand number of records filtered by WHERE conditions.
    – a_vlad
    Nov 15 '16 at 21:07
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.