1

I have table with user transactions.I need to select users who made total transactions more than 100 000 in a single day.Currently what I'm doing is gather all user ids and execute 

SELECT sum ( amt ) as amt from users where date = date("Y-m-d") AND user_id=id;

for each id and checking weather the amt > 100k or not.

Since it's a large table, it's taking lot of time to execute.Can some one suggest an optimised query ?

James Z
  • 12,209
  • 10
  • 24
  • 44
Harikrishnan
  • 9,688
  • 11
  • 84
  • 127
  • `select user_id,sum(amt) as amount from table where date=date("Y-M-D") group by user_id having amount>100000` – vhadalgi Dec 13 '13 at 05:08

5 Answers5

1

This will do:

SELECT sum ( amt ) as amt, user_id from users
where date = date("Y-m-d") 
GROUP BY user_id
HAVING sum ( amt ) > 1;   ' not sure what Lakh is
cha
  • 10,301
  • 1
  • 18
  • 26
1

What about filtering the record 1st and then applying sum like below

select SUM(amt),user_id from (
SELECT  amt,user_id from users where user_id=id date = date("Y-m-d") 
)tmp 
group by user_id having sum(amt)>100000
sumit
  • 15,003
  • 12
  • 69
  • 110
1
  1. What datatype is amt? If it's anything but a basic integral type (e.g. int, long, number, etc.) you should consider converting it. Decimal types are faster than they used to be, but integral types are faster still.
  2. Consider adding indexes on the date and user_id field, if you haven't already.

  3. You can combine the aggregation and filtering in a single query...

    SELECT SUM(Amt) as amt 
FROM users 
WHERE date=date(...) 
  AND user_id=id 
GROUP BY user_id
HAVING amt > 1
schufty
  • 131
  • 1
  • 1
  • 7
  • `1)It's float` `2)Will do` `3)Ok` – Harikrishnan Dec 13 '13 at 05:12
  • For monetary amounts, you should definitely switch to a Decimal data type at least. See this [answer](http://stackoverflow.com/a/3768908/444013). If you're trying to ultra-optimize your query, you should switch to an integral type (e.g. bigint) and do the decimal conversions in your display code. – schufty Dec 13 '13 at 05:33
1

The only optimization that can be done in your query is by applying primary key on user_id column to speed up filtering.

As far as other answers posted which say to apply GROUP BY on filtered records, it won't have any effect as WHERE CLAUSE is executed first in SQL logical query processing phases. Check here

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
0

You could use MySql sub-queries to let MySql handle all the iterations. For example, you could structure your query like this:

select user_data.user_id, user_data.total_amt from 
(
    select sum(amt) as total_amt, user_id from users where date = date("Y-m-d") AND user_id=id
) as user_data
where user_data.total_amt > 100000;
Neel Borooah
  • 91
  • 1
  • 1
  • 11
  • this will execute ever slower than OPs sql. because u have wrapped OP's query in the inner query – sumit Dec 13 '13 at 05:16