Optimization, speed in select, and PHP code

Asked

Viewed 381 times

2

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:

inserir a descrição da imagem aqui

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.

2 answers

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.

  • Good Inkeliz, I left as it is, the followers sent I did a check to see if my id and other id of the user q followed me, does not exist in the table, I think this already helps. i can’t make select by iD like you said, you’ll always have gaps and that’s unfeasible.

  • You can create a specific table for this, after all Mysql itself creates gaps (the use of TRANSACTION is an example).

  • I am seeing here a way, I understood your answer but I am trying to do before I ask any other question. I am with many doubts. :/

1

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;
}
  • And the question of scanning the whole table? Won’t it? , regarding the index, this way will not work, you selected wrong this way will bring me some error like: Unknown column 'idx_user' in 'field list'

  • yes, the script will only get any number between 0 and the total of accounts , and soon after do a query, that instead of doing a scan across the table , will pick up just a record according to the n° generated previously.

  • Yes I understood the logic, but the right would not be to select Count(*) from table use index (idx_user) ?

  • this works the same as Rand() in the msm query.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.