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.– user94336
You can create a specific table for this, after all Mysql itself creates gaps (the use of
TRANSACTION
is an example).– Inkeliz
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. :/
– user94336