Php query lagging server

Question

So i have a rather big query which searched though 6k members and 3k websites this use to be good on website start up but now the site is getting huge the page which does this query has started to lag just looking for suggestions on how i could speed it up

$stmt212 = $db->prepare('SELECT * 
FROM websites w
    LEFT JOIN users u ON u.username = w.owner
WHERE u.coins >= ? 
ORDER BY RAND() 
LIMIT 1');
$stmt212->execute( array('1') ) ;
$row212 = $stmt212->fetch();

The users have "coins" on my website and items they earn coins and then there item gets viewed so what im doing above is grabbing a user where there coins is more than 1 and who has a item

^lol didn't even see that, order by rand is performance death http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand — , Feb 19 '17 at 01:16
Possible duplicate of [When does MySQL ORDER BY RAND() function order?](http://stackoverflow.com/questions/6910613/when-does-mysql-order-by-rand-function-order) — Matt S, Feb 19 '17 at 01:21
i removed the ORDER BY RAND and the speed has increased a lot — nicholas davies, Feb 19 '17 at 01:29
no one said simply remove it, because that changes the resutls you get. — , Feb 19 '17 at 01:32
so whats the best thing todo?If i still want a random / different result each time — nicholas davies, Feb 19 '17 at 01:34
@nicholasdavies do you have an id as the primary key? You can create a random id value in php and then pass that to the sql select statement. — Ray Hunter, Feb 19 '17 at 01:41
You can see http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function to learn how to optimize ORDER BY RAND() in mysql — Haotian Liu, Feb 19 '17 at 01:46
i have a id field / collum but no primary key set i have a id column inside both tables that im selecting from — nicholas davies, Feb 19 '17 at 01:47

Xorifelse · Answer 1 · 2017-03-14T04:45:56.170

As stated in most comments, using RAND() in a query can be a bad thing. I'm presuming both columns are not indexed, this makes it the database driver very difficult.

To keep your database structure and to maintain performance, you can let PHP randomize your indexes for you:

$stmt = $db->prepare('
  SELECT * 
  FROM websites w
  LEFT JOIN users u ON u.username = w.owner
  WHERE u.coins >= ?
');

$stmt->execute(array('1')); // why are you not checking if this succeeds?
$result = $stmt->fetchAll(PDO::FETCH_NUM);
$result = array_rand($result);

print_r($result);

score 0 · Answer 2 · answered Feb 19 '17 at 09:35

You'll need to do with with two queries instead of one.

Get the number of records that match your criteria

$stmt212count = $db->prepare("
    SELECT
        count(*)
    FROM
        websites w
    INNER JOIN
        users u ON
            u.username = w.owner
            AND u.coins >= :coins
");
$stmt212count->bindValue('coins', 1, PDO::PARAM_INT);
$stmt212count->execute();
$row212count = $stmt212count->fetch(PDO::FETCH_COLUMN);

Pick a random row

# random row offset
$offset = rand(0, $row212count-1);

use this statement if you have PDO emulated prepares turn on

$stmt212 = $db->prepare(sprintf(
    "
        SELECT
            *
        FROM
            websites w
        INNER JOIN
            users u ON
                u.username = w.owner
                AND u.coins >= :coins
        LIMIT %d,1
    ",
    $offset
);
$stmt212count->bindValue('offset', $offset, PDO::PARAM_INT);

use this if you aren't using PDO emulated prepares

$stmt212 = $db->prepare("
    SELECT
        *
    FROM
        websites w
    INNER JOIN
        users u ON
            u.username = w.owner
            AND u.coins >= :coins
    LIMIT :offset,1
");

use this for both statements

$stmt212count->bindValue('coins', 1, PDO::PARAM_INT);
$stmt212count->execute();
$row212 = $stmt212->fetch();

Php query lagging server

2 Answers2