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