Rand() optimization

[Albanj]

Information

• Version of Medoo: 2.1.7
• Type of Database (MySQL, MSSQL, SQLite...): Mysql
• Amount of lines in my database: 200 k
• Weight of my database: 500 Mo

Hello folks.

I make a

$var = $database->rand('xxx', [
    'yyy',
    'zzz'
    ],[
    'LIMIT' => 6
    ]);

My hoster is crying because the request need to load a very big amount of data for every page load and overload the temporaries files.

They explain to me how to optimize the SQL request. If I understood how SQL woks, I won't use Medoo. No ? And we passed from (generated by Medoo)

SELECT yyy,zzz
FROM xxx
ORDER BY RAND()
LIMIT 6;

to

SELECT s.yyy, s.zzz
FROM xxx s
JOIN (
    SELECT id
    FROM xxx
    ORDER BY RAND()
    LIMIT 6
) AS z ON z.id = s.id

The time of request is 4s for unoptimized request to 0.15s for the optimized. The idea come from : https://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand

I clearly don't have the skill to make a pull request, but I think this optimization in Medoo could be a good idea. In reality, I don't have the skill to understand how and why this optimization works.

Regards

[catfan]

Well, optimizing fetching data randomly from the database is a complex case.

Those solutions are based on different construction, different data type, or different database engine. There is no absolute answer.

I just recommend you can use rand() for simple and small data.

But for complex and larger data, it's good to write your query alone with query() based on your actual situation to get the best performance.

[Albanj]

Ok, thank you for your enlightenment.

And yes, I used query() yesterday to implement the solution prescribed by my hoster.