PHP：for循环使VPS上的页面加载速度非常慢

Question

I'm new to php so I'm having trouble seeing where I'm going wrong with this code. I'm trying to pull a set of 4 random IDs to display their images and links on the page. Running the same code on my local dev environment works fine and the response is quick. On my VPS, however, the page will take 15-30 seconds to load. I managed to isolate the issue to this loop by just taking the whole block out and the page loads quickly again.

<?php
$alreadypicked = array();
for ($i = 1; $i <= 4; $i + 0) {
    $randchoice = getinfoForRandom();
    $info = pullrandinfo($randchoice);
    if (!in_array($randchoice, $alreadypicked)) {?>
            <div class="col-sm-3 col-xs-6">
                <a href="<?php echo 'recipes.php?id=' . $info[0]['id'];?>">
                    <img class="img-responsive portfolio-item" src="<?php echo 'images/' . $info[0]['filename'];?>" alt="<?php echo $info[0]['name'];?>">
                </a>
            </div>
        <?php
        array_push($alreadypicked, $randchoice);
        } else {
            $i = $i - 1;
            }
        }?>

I tried doing the same with a while loop instead of an if/else statement, but that did not fix the slow loading.

Other relevant parts of code:

// Gets all IDs in database
function getIDs() {
    $handler = dbconnector();

    $query = $handler->query('SELECT id FROM dishes');
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $output = $query->fetchAll();
    $array = array();
    for ($i = 0; $i < count($output); $i++) {
        array_push($array, $output[$i]['id']);
    }
    return $array;
}

// Chooses a random ID that exists
function getinfoForRandom() {
    $IDs = getIDs();
    $totalIDs = count($IDs);

    do {
        $random = mt_rand(1, max($IDs));
    } while (!in_array($random, $IDs));

    return $random;
}

// Pulls data for the randomly selected ID
function pullrandinfo($id) {
    $handler = dbconnector();

    $query = $handler->query('SELECT id, name, filename FROM dishes WHERE id = ' . $id);

    $query->setFetchMode(PDO::FETCH_ASSOC); // Fetches only assoc array from the query above

    $data = $query->fetchAll();
    return $data;
}

Answer 1

You can improve performance by limiting the number of queries that you execute.

Your code appears to execute two queries in each loop, one in getIds() and the other in pullrandinfo(). These queries are expensive and time-consuming.

Instead, fetch your IDs in a single query making use of SQL's RAND().

SELECT id FROM <table> ORDER BY RAND() LIMIT 4;

Then fetch all of the IDs in a single query:

'SELECT ... WHERE id IN (' . implode(',', $ids) . ')...'

Finally, use your loop only to output the data.