How to reduce execution time?

Question

I have a facebook app where users can bet on a basketball game (guess the winner, the difference, the mvp and the top scorer).

I keep one table with the games, one with the IDs of the users and one with the bets.

If you guess the winner right, you get 1 point. If you guess the difference right, you get 5 points. If you guess the mvp right you get 3 points, and the same goes with the top scorer.

Now I have a file that makes a table with all the users and their points:

        <table>
        <tr>
            <td></td>
            <td>
                Name
            </td>
            <td>
                Points
            </td>
        </tr>
        <?php
            $selectusers = $db->query("SELECT * FROM `predictorusers`");
            $c = 0;
            while ($appuser = $db->fetchRows($selectusers))
            {
                try
                {
                    $profile = $facebook->api('/'.$appuser['userid']);
                } catch (FacebookApiException $e) {
                    continue;
                }
                $c++;

                $points = 0;

                $selectbets = $db->query("SELECT * FROM `predictor` WHERE `userid`='{$profile['id']}'");
                while ($bet = $db->fetchRows($selectbets))
                {
                    $selectgame = $db->query("SELECT * FROM `schedule` WHERE `id`='{$bet['gameid']}'");
                    $game = $db->fetchRows($selectgame);

                    if ($bet['winner'] == 1 && $game['homescore'] > $game['awayscore'])
                    {
                        $points = $points + 1;

                        if (($game['homescore'] - $game['awayscore']) == $bet['diff'])
                            $points = $points + 5;
                    }
                    elseif ($bet['winner'] == 2 && $game['awayscore'] > $game['homescore'])
                    {
                        $points = $points + 1;

                        if (($game['awayscore'] - $game['homescore']) == $bet['diff'])
                            $points = $points + 5;
                    }

                    $selectmvprkg = $db->query("SELECT MAX(`rkg`) FROM `boxscore` WHERE `game`='{$game['id']}'");
                    $mvprgk = $db->fetchRows($selectmvprkg);

                    $selectmvp = $db->query("SELECT * FROM `boxscore` WHERE `rkg`='{$mvprkg['rkg']}'");
                    while ($mvp = $db->countRows($selectmvp))
                    {
                        if ($mvp['player'] == $bet['mvp'])
                            $points = $points + 3;
                    }

                    $selecttopscorerpts = $db->query("SELECT MAX(`pts`) FROM `boxscore` WHERE `game`='{$game['id']}'");
                    $topscorerpts = $db->fetchRows($selecttopscorerpts);

                    $selecttopscorer = $db->query("SELECT * FROM `boxscore` WHERE `pts`='{$topscorerpts['pts']}'");
                    while ($topscorer = $db->fetchRows($selecttopscorer))
                    {
                        if ($topscorer['player'] == $bet['topscorer'])
                            $points = $points + 5;
                    }
                }
        ?>
        <tr>
            <td>
                <?php echo $c; ?>
            </td>
            <td>
                <?php echo $profile['name']; ?>
            </td>
            <td>
                <?php echo $points; ?>
            </td>
        </tr>
        <?php
            }
        ?>
    </table>

The only problem, is that the script takes more than 30 seconds to be fully executed, so the server stops it's execution and sends an erro:

Fatal error: Maximum execution time of 30 seconds exceeded in * on line 37

Does anyone know how can I prevent this from happening?

Edit:

Take a look on this file that contains 0 api calls:

        <table>
        <tr>
            <td></td>
            <td>
                Name
            </td>
            <td>
                Points
            </td>
        </tr>
        <?php
            $selectusers = $db->query("SELECT * FROM `predictorusers`");
            $c = 0;
            while ($appuser = $db->fetchRows($selectusers))
            {
                $c++;

                $points = 0;

                $selectbets = $db->query("SELECT * FROM `predictor` WHERE `userid`='{$appuser['userid']}'");
                while ($bet = $db->fetchRows($selectbets))
                {
                    $selectgame = $db->query("SELECT * FROM `schedule` WHERE `id`='{$bet['gameid']}'");
                    $game = $db->fetchRows($selectgame);

                    if ($bet['winner'] == 1 && $game['homescore'] > $game['awayscore'])
                    {
                        $points = $points + 1;

                        if (($game['homescore'] - $game['awayscore']) == $bet['diff'])
                            $points = $points + 5;
                    }
                    elseif ($bet['winner'] == 2 && $game['awayscore'] > $game['homescore'])
                    {
                        $points = $points + 1;

                        if (($game['awayscore'] - $game['homescore']) == $bet['diff'])
                            $points = $points + 5;
                    }

                    $selectmvprkg = $db->query("SELECT MAX(`rkg`) FROM `boxscore` WHERE `game`='{$game['id']}'");
                    $mvprgk = $db->fetchRows($selectmvprkg);

                    $selectmvp = $db->query("SELECT * FROM `boxscore` WHERE `rkg`='{$mvprkg['rkg']}'");
                    while ($mvp = $db->countRows($selectmvp))
                    {
                        if ($mvp['player'] == $bet['mvp'])
                            $points = $points + 3;
                    }

                    $selecttopscorerpts = $db->query("SELECT MAX(`pts`) FROM `boxscore` WHERE `game`='{$game['id']}'");
                    $topscorerpts = $db->fetchRows($selecttopscorerpts);

                    $selecttopscorer = $db->query("SELECT * FROM `boxscore` WHERE `pts`='{$topscorerpts['pts']}'");
                    while ($topscorer = $db->fetchRows($selecttopscorer))
                    {
                        if ($topscorer['player'] == $bet['topscorer'])
                            $points = $points + 5;
                    }
                }
        ?>
        <tr>
            <td>
                <?php echo $c; ?>
            </td>
            <td>
                <?php echo $app['userid']; ?>
            </td>
            <td>
                <?php echo $points; ?>
            </td>
        </tr>
        <?php
            }
        ?>
    </table>

The same thing happens.

Answer 1

1. Please dont use select * , instead use specific column names.
2. Please avoid nested sql statements by using single fetch and save data in an array for traversing.
3. Try using XDebug Profiler to figure out which part of script is taking more time to load.

Answer 2

I'd suggest caching the results of your API calls in some fashion, so that you don't have to make a call to the API for each user every single time the page loads.

You may also want to look into the Facebook API functions that allow you to request data for more than a single user at a time - making one API call for all of the users in your result set would be much faster than making individual API calls for each user.

Also in general, nesting queries in loops multiple levels deep is a bad idea - try to reduce the total number of queries you're running.

Answer 3

You have nested queries 3 deep. Nested queries in code is always a red flag (on rare occasions necessary). For every record in predictorusers you are querying records in predictor. Then for every query in predictor you are running queries against schedule and boxscore (3 times).

That's where your problem is, you have way too many queries running. You should be using JOINs and narrowing the data down to specific information. It seems like you are querying every piece of data you have. You can probably get it down to 1 query per user, or even just 1 query with a userid filter.