Optimization, speed in select, and PHP code

Question

Following the reasoning of this question, i made this method, where I select several users at once to return me only 1 in random order, ie, my query that is in the function does a scan on the whole table to return me only one result, this is overloading my server, where I have more than 2,000 registered accounts.

Here is my code:

public function random($username) {
    if ($this->vip($username)) {
        $sql = "SELECT * FROM `viperusers` WHERE vip < 1 ORDER BY RAND() LIMIT 1";
    } else {
        $sql = "SELECT * FROM `viperusers` WHERE vip = 0 ORDER BY RAND() LIMIT 1";
    }

    $query = $this->_db->select($sql);

    if (empty($query[0]->username)) {
        return $this->random($username);
    }

    return $query;
}

Yeah, I know I should wear it indexes in the mysql, and I got it, look:

I can find no other way, to do without using RAND() in my SELECT, remembering that I have another table called vipersends that saves my ds_user_id and the ds_user_id of the other user. It’s overloading.

Answer 1

This can have several reasons, including the very way that organized the database or even the hardware. Running a test, in a table containing 239289977 lines and more than 46GB, the SELECT * FROM tabela ORDER BY RAND() LIMIT 1 took exactly 00:02:07. It is a great time, obviously, but the number of registrations is much higher than 2000, with this amount I believe that the time would be as fast as one SELECT conventional.

The RAND() can collaborate with slowness, since it creates a temporary table. Temporary tables are saved to disk (SSD/HDD) and this will destroy any performance. (I don’t know if this has been modified in recent versions of Mysql!). You can force Mysql to use RAM, but as long as you have enough RAM, you can use:

SELECT SQL_SMALL_RESULT WHERE vip = 0 ORDER BY RAND() LIMIT 1

This is also limited by tmp_table_size, configured on my.cnf. In general, this will not change the situation much.

The indices will be ignored in the temporary table, that is, the only index that can import is the vip, only.

---

A better solution requires you to have sequential data, all 2000 values must be from 0 to 1999, without any gap and all of them are selectable (which is not the case).

Then just do:

$maxsql = mysqli_query($conn, 'SELECT id FROM tabela ORDER BY id DESC LIMIT 1');
list($max) = mysqli_fetch_row($maxsql);


$random = random_int(0, $max);
$sql = 'SELECT * FROM tabela WHERE vip = 0 AND id >= ' . random;
//...

But in your case you have so much vip = 0 and the vip <1, which makes it unviable because it becomes non-uniform. Consider that the id=999 be it vip=-1, it cannot be selected in the above situation, it will cause the id=1000 be selected both with the $random=999 as to the $random=1000.

This can be solved by creating two separate tables, one tabela_normal and another tabela_vip.

---

In addition, in the end you can also pre-compute the data that are costly. If you have LIMIT 1, but performs it often, it would be better to generate once a LIMIT 50 and store. The cost of a SELECT is much less, in your case, than the cost of the SELECT ... RAND, so reading a previously generated data is much faster. This would drastically reduce the number of requests. Again, this is in the architecture itself, how you built this.

Answer 2

You can take n° of accounts, and use php Rand().

public function random($username) {  


    $sql = "SELECT count(idx_user) FROM `viperusers`" ;
    $query = $this->_db->select($sql);
    $total_de_contas = $query[0];
    $rr = rand(0, $total_de_contas);


    $sql = "SELECT * FROM `viperusers` WHERE idx_user = $rr";

    $query = $this->_db->select($sql);

    if (empty($query[0]->username)) {
        return $this->random($username);
    }

    return $query;
}