MySQL Month and Year grouping slow performance

user_1706202 0
Viewed 399

I've got a report which I need to show the month and year profit from my transaction the query which I made and works is very slow and can not figure out how I can manage to change the query the way that consumes less time to load.

SELECT MONTH(MT4_TRADES.CLOSE_TIME) as MONTH
     , YEAR(MT4_TRADES.CLOSE_TIME) as YEAR
     , SUM(MT4_TRADES.SWAPS) as SWAPS
     , SUM(MT4_TRADES.VOLUME)/100 as VOLUME
     , SUM(MT4_TRADES.PROFIT) AS PROFIT 
  FROM MT4_TRADES 
  JOIN MT4_USERS 
    ON MT4_TRADES.LOGIN = MT4_USERS.LOGIN 
 WHERE MT4_TRADES.CMD < 2  
   AND MT4_TRADES.CLOSE_TIME <> "1970-01-01 00:00:00" 
   AND MT4_USERS.AGENT_ACCOUNT <> "1" 
 GROUP 
    BY YEAR(MT4_TRADES.CLOSE_TIME)
     , MONTH(MT4_TRADES.CLOSE_TIME) 
 ORDER 
    BY YEAR

This is the full query, any suggestion would be highly appreciated.

This is the result of explain:

enter image description here

mysql performance
1 Answers

Echoing the comment from @Barmar, look at the EXPLAIN output to see query execution plan. Verify that suitable indexes are being used.

Likely the big rock in terms of performance is the "Using filesort" operation.

To get around that, we would need a suitable index available. and that would require some changes to the table. (The typical question on "improve query performance" topic on SO comes with a restrictions that we "can't add indexes or make any changes to the table".)

I'd be looking at a functional index (feature added in MySQL 8.0, for MySQL 5.7, I'd be looking at adding generated columns and including generated columns in a secondary index, featured added in MySQL 5.7)

CREATE INDEX `MT4_TRADES_ix2` ON MT4_TRADES ((YEAR(close_time)),(MONTH(close_time)))

I'd be tempted to go with a covering index, and also change the grouping to a single expression e.g. DATE_FORMAT(close_time,'%Y-%m')

CREATE INDEX `MT4_TRADES_ix3` ON MT4_TRADES ((DATE_FORMAT(close_time,'%Y-%m'))
  ,swaps,volume,profit,login,cmd,closetime)

from the query, it looks like login is going to be UNIQUE in MT4_USERS table, likely that's the PRIMARY KEY or a UNIQUE KEY, so an index is going to be available, but we're just guessing...

With suitable indexes available, we could so something like this:

SELECT DATE_FORMAT(close_time,'%Y-%m')                                    AS close_year_mo
     , SUM(IF(t.cmd < 2 AND t.close_time <> '1970-01-01', t.swaps  ,NULL))      AS swaps
     , SUM(IF(t.cmd < 2 AND t.close_time <> '1970-01-01', t.volume ,NULL))/100  AS volume
     , SUM(IF(t.cmd < 2 AND t.close_time <> '1970-01-01', t.profit ,NULL))      AS profit 
  FROM MT4_TRADES t
  JOIN MT4_USERS u
    ON u.login = t.login 
   AND u.agent_account <> '1' 
 GROUP BY close_year_mo
 ORDER BY close_year_mo

and we'd expect MySQL to do a loose index scan, with the EXPLAIN output top show "using index for group-by" and not show "Using filesort"

EDIT

For versions of MySQL before 5.7, we could create new columns, e.g.year_close and month_close, populate the columns with the results of expressions YEAR(close_time) and MONTH(close_time) (we could create BEFORE INSERT and BEFORE UPDATE triggers to handle that automatically for us)

Then we could create index with those columns as the leading columns

 CREATE INDEX ...  ON MT4_TRADES ( year_close, month_close, ... )

And then reference the new columns in the query

SELECT t.year_close  AS `YEAR`
     , t.month_close  AS `MONTH`

  FROM MT4_TRADES t
  JOIN ...
 WHERE ...
 GROUP
    BY t.year_close
     , t.month_close

Ideally include in the index all of referenced columns from MT4_TRADES, to make a covering index for the query.

user_1077440
Related