MySQL query runs out of memory

Question

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.

Run an `EXPLAIN` plan in MySQL for the query. It may hint at a missing index. — bcmcfc, Nov 16 '16 at 19:11
right now I am just trying to run the raw query that I posted directly in mysql server using mysql workbench and running out of memory there. — Waqar Sadiq, Nov 16 '16 at 19:14
@WaqarSadiq stick `EXPLAIN ` on the front of it and post the results. — bcmcfc, Nov 16 '16 at 19:15
I did that and it gave me back three rows but did not indicate any full table scans or anything like that. — Waqar Sadiq, Nov 16 '16 at 19:16
Please add it to your question. It is just guesswork at answering otherwise. — bcmcfc, Nov 16 '16 at 19:20
If your server runs out of memory it's configured incorrectly. Check the size of your InnoDB buffer pools. Maybe they're too generous. — tadman, Nov 16 '16 at 19:32

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

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

edited May 23 '17 at 12:07

Community

1
1

answered Nov 16 '16 at 19:17

CLAbeel

1,078
14
20

You are right. That is what was happening. Thanks. – Waqar Sadiq Nov 16 '16 at 19:20
Was that causing the memory issue, because so many records were coming back? – CLAbeel Nov 16 '16 at 19:21
I think so. Once I put those parenthesis around - it worked fine without memory exhaustion. – Waqar Sadiq Nov 16 '16 at 19:46

MySQL query runs out of memory

1 Answers1