CODEWITHSUNDEEP
phpmysqlsqlmysqli
D_R
D_R

Reputation: 4962

Improving query run time (Takes more than 20seconds to load a page)

I've got a query which is loaded everytime the user open his profile page. and its really slow. it takes more than 20 seconds to load the page. its kinda simple query, but with alot of lines so don't be scared looking at it. :)

I would appericiate any help on improving my query.

SELECT 
    `h`.`login` AS `login`,
    SUM(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`pips`,NULL)) AS `total_pips`,
    COUNT(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`position_num`,NULL)) AS `total_trades`,
    (COUNT(IF(((`h`.`pl` > 0) AND ((`h`.`cmd` = 0) OR (`h`.`cmd` = 1))),`h`.`pl`,NULL)) / COUNT(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`position_num`,NULL))) AS `winning_trades_percent`,
    SUM(if(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`gain`,NULL)) AS `total_gain`,
    (SELECT AVG(`wg`.`weekly_gain_all`) FROM `gt_view_weekly_gain` `wg` where (`wg`.`login` = `h`.`login`) group by `wg`.`login`) AS `weekly_gain`,
    AVG(IF(((`h`.`pips` > 0) AND ((`h`.`cmd` = 0) OR (`h`.`cmd` = 1))),`h`.`pips`,NULL)) AS `average_profit_pips`,
    AVG(IF(((`h`.`pips` <= 0) AND ((`h`.`cmd` = 0) OR (`h`.`cmd` = 1))),`h`.`pips`,NULL)) AS `average_lose_pips`,
    AVG(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`pips`,NULL)) AS `average_pips`,
    AVG(time_to_sec(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),timediff(`h`.`close_time`,`h`.`open_time`),NULL))) AS `average_trade_time`,
    STD(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`pips`,NULL)) AS `volatility`,
    MAX(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`pips`,NULL)) AS `best_trade`,
    MIN(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`pips`,NULL)) AS `worst_trade`,
    (ceiling(((ceiling(to_days(now())) - ceiling(to_days(MIN(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`open_time`,NULL))))) / 7)) - 1) AS `running_weeks`,
    (SELECT `fn`.`followers_num_all` FROM `gt_view_followers_num` `fn` where ((`fn`.`guru_id` = `h`.`guru_or_guru_user_id`) AND (`h`.`is_guru_history` = 1))) AS `followers_num`,
    SUM(IF(((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)),`h`.`pl`,NULL)) AS `total_profit_loss`,
    SUM(IF(((`h`.`cmd` = 6) AND (`h`.`pl` > 0)),`h`.`pl`,NULL)) AS `deposits`,
    SUM(IF(((`h`.`cmd` = 6) AND (`h`.`pl` < 0)),`h`.`pl`,NULL)) AS `withdrawls`,
    COUNT(IF((((`h`.`cmd` = 0) OR (`h`.`cmd` = 1)) AND (`h`.`pl` > 0)),`h`.`position_num`,NULL)) AS `total_winning_trades_count`,
    ABS((AVG(IF(((`h`.`pips` > 0) AND ((`h`.`cmd` = 0) OR (`h`.`cmd` = 1))),`h`.`pips`,NULL)) / AVG(IF(((`h`.`pips` <= 0) AND ((`h`.`cmd` = 0) OR (`h`.`cmd` = 1))),`h`.`pips`,NULL)))) AS `average_profit_loss_ratio` 
FROM 
    `gt_history` `h` 
WHERE
    `h`.`is_closed` = 1
GROUP BY
    `h`.`login`

Upvotes: 0

Views: 93

Answers (2)

DB_learner
DB_learner

Reputation: 1026

Instead of using "(h.cmd = 0) OR (h.cmd = 1)" in every columns IF, try filtering it in where clause. It might improve, since data selection is reduced.

Upvotes: 0

R&#233;mi
R&#233;mi

Reputation: 3745

It seems this query computes the values for all the users. If you wish to display this for only one user, then it might be much faster to add and (login='xxx') in the where clause.

Otherwise, as Mihai suggested, caching might be the best approach.

Upvotes: 2

Related Questions