CODEWITHSUNDEEP
phpmysqlperformancequery-optimization
Roman Newaza
Roman Newaza

Reputation: 11700

Is it better to move values computation to PHP layer out of SQL?

This method is to select number of actions between User and TargetUser, e.g. just between two users. The result values of this method are dependent of GetTotalOfPossibleActions() return value which is dynamic (every user has its own number).

Question: Is it better to move values computation to PHP layer out of SQL?

public function GetAction() {

    // ...

    $MaxActionCount = $this->GetTotalOfPossibleActions();

    return registry::getInstance()->get('DB')->select(
        'SELECT
            `Action`
            , `HA`.`Id` AS `ActionId`
            , IF(`Count` IS NULL
                , IF('.$MaxActionCount.' % 2
                    , IF(`HA`.`Id` = 1
                        , CEIL('.$MaxActionCount.' / 2)
                        , FLOOR('.$MaxActionCount.' / 2))
                    , '.$MaxActionCount.' / 2)
                , GREATEST(IF('.$MaxActionCount.' % 2
                    , IF(`HA`.`Id` = 1
                        , CEIL('.$MaxActionCount.' / 2) -  CONVERT(`H`.`Count`, SIGNED)
                        , FLOOR('.$MaxActionCount.' / 2) - CONVERT(`H`.`Count`, SIGNED))
                    , '.$MaxActionCount.' / 2 - `H`.`Count`), 0)
            ) AS `CountLeft`
        FROM `Help` AS `H`
        RIGHT JOIN `HelpAction`  AS `HA`
        ON `H`.`ActionId` = `HA`.`Id`
        AND `UserId` = '.$this->UserId.'
        AND `TargetUserId` = '.$this->TargetUserId.'
        AND `CreatedDate` = CURDATE()'));

}

Upvotes: 1

Views: 116

Answers (1)

Robbie
Robbie

Reputation: 17720

There is no hard and fast answer, but the following is a general guide I apply.

PHP servers can be load balanced and multiplied. So if you're running short on CPU cycles, you can add another PHP server relatively easily. Conversely, MySQL server can't be easily multiplied. You can add replication servers and run complex "selects" on the slave servers, but replication adds a little stress and there is always a delay between an update on the master, and it being available on the slave. (Never believe the DB admin that says it's milliseconds; and it gets slower when the server is under stress / with backups etc.)

So +1 to PHP.

However, SQL is built and designed to do computations with the data. That's it's job. It's much more efficient at it (assuming you design the tables, queries and indexes correctly).

So +1 to mySQL.

If you do the computation on MySQL, you also don't need to transfer the entire data table to PHP to process and handle. This saves netwerk traffic between the two.

So +1 to mySQL.

But if you're struggling to get the queries right, or MySQL is using up all your memory, non-stop creating temporary tables and swapping to hard disk, it's a lot easier to debug and find the problem by breaking down the computation in PHP. MySQL EXPLAIN and slow query logs are frustrating to decypher sometimes.

So +1 to PHP.

So... 2 all. If you have both on one server, and the queries are not giving you problems, let MySQL do it's job. If you are having problems with queries, pull back into PHP. If you're on multiple servers, do as much in MySQL as you can without reaching a bottleneck. If MySQL is a bottleneck and replication not possible, pull back into PHP. But then check network traffic.

Then remember to re-evaluate when your load increases...

Upvotes: 2

Related Questions