0

If I have a query like 

select sum(number) as points, user from point
where expiryDate < NOW()
group by user
limit 1;

will the DB need to process the entire point table even though it will only need to return 1 row (or afew rows).

Some background:

Users have points that can expire, when points expire, I want to process it. Here I am querying for expired points for each user. Since there are possibly many such points, I am trying to do this process in batches, thus the LIMIT. But my guess is this does not actually optimize anything if the DB searches the entire DB anyways. Is there a better way?

Rohit Gaikwad
  • 817
  • 2
  • 8
  • 24
Jiew Meng
  • 84,767
  • 185
  • 495
  • 805
  • probably, yeah. but remove the "group by user" part, and it shouldn't – hanshenrik Sep 15 '15 at 08:35
  • GROUP BY results in an implicit ORDER BY (although deprecated from MySQL 5.6 it would seem). As such it would seem that it will likely process the full result set before applying the limit. – Kickstart Sep 15 '15 at 08:55

0 Answers0