php - I need to get sum of income for each user in January. Which of these 3 MySQL queries is more efficient? -

i need sum of income each user in january 2016 represented payments , commissions, not deposits. table of records looks (millions of records):

username  amount   transaction        date user_1       1     deposit #1       2015-12-12 23:19:00 user_2       2     payment #2       2015-12-23 13:45:00 user_2       7     commission #3    2016-01-04 23:45:00 user_1       1     payment #4       2016-01-09 20:25:00 user_3       2     payment #5       2016-01-17 02:42:00 user_1       3     commission #6    2016-01-23 18:30:00 user_3       2     deposit #5       2016-01-29 09:58:00 ...         ...    ...              ... 

my 3 queries are:

select username, sum( amount ) monthly_amount balance_history         month( date ) = 1      , year( date ) = 2016      , (transaction 'payment%' or transaction 'commission%') group username 

or

select username, sum( amount ) monthly_amount balance_history         '2015-12-31 23:59:59' < date , date < '2016-02-01 00:00:00'      , (transaction 'payment%' or transaction 'commission%') group username 

or

select username, amount, transaction         balance_history         '2015-12-31 23:59:59' < date , date < '2016-02-01 00:00:00'       , doing sorting, sum, , other calcs php 

i go 3rd one, appreciate other inputs.

also, there tool measure "efficiency" of query?

thank , happy new year!

if there no index on date column, 1st , 2nd query should take approximately same amount of time (as both scan full table)

i not go third 1 : if have lot of data, can possibly reach php memory limit, furthermore mysql have algorithms calculations want do, why not using them ? :-)

however suggest create index on column date

alter table `balance_history`  add index `idx_date` (`date` asc); 

important note : depending on production server performance , load block table several minutes - test on development environment first !

once index created, second query efficient 1 :

select username, sum( amount ) monthly_amount balance_history         '2015-12-31 23:59:59' < date , date < '2016-02-01 00:00:00'      , (transaction 'payment%' or transaction 'commission%') group username 

if use month( date ) or year( date ) in clause index on date useless : scanning full table ...