Waqar Sadiq Waqar Sadiq - 4 months ago 30
MySQL Question

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</code>

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.


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?.