CODEWITHSUNDEEP
phpmysqllaravel
Waqar Sadiq
Waqar Sadiq

Reputation: 135

MySQL query runs out of memory

I have a system in which a store is an account and customers shop in those stores. There is a table that stores many-to-many association of customers and stores. The key attributes of that table are accountid, customerid and last_visit_date. For a set of accountids, I need to find the most recent visit of each customer. I have a query that works perfectly but seems to be inefficient because it runs out of memory for about 21000 customers.

SELECT ac.customerId FROM account_customer ac 
      INNER JOIN (SELECT customerId, max(last_visit_date) AS 
                     LastVisitDate FROM account_customer 
                  WHERE accountId in        
                     (311,307,318,320,321,322,323,332,347,439,519,630,634,643) 
                 GROUP BY customerId) grouped_ac 
     ON ac.customerId = grouped_ac.customerId 
          AND ac.last_visit_date = grouped_ac.LastVisitDate 
          AND ac.last_visit_date <= '2016-10-18' 
          OR ac.last_visit_date is null

When I run the above query, it gives me the correct result for a smaller dataset but for larger dataset, I get memory error. I am not even talking about a very large set - just around 20,000 + customers.

Any help would be appreciated.

Upvotes: 2

Views: 666

Answers (1)

CLAbeel
CLAbeel

Reputation: 1088

Do you possibly mean 

ac.customerId = grouped_ac.customerId 
AND ac.last_visit_date = grouped_ac.LastVisitDate 
and (ac.last_visit_date <= '2016-10-18' or ac.last_visit_date is null)

I think without the parentheses, the query may be returning all records there the last_visit_date is null.

Take a look at the answer to How exactly does using OR in a MySQL statement differ with/without parentheses?.

Upvotes: 1

Related Questions