I need to get some data from tables and order them by two parameters. One of the parameters are RAND() because records needs to be random. I have large database and using RAND() slows down performance alot and I want to avoid it. Is there any chance to optimize performance of random ordering ? I need to order my result based on count (lowest to highest) and then I need to perform ordering by RANDOM.
This is query that I am using
SELECT c.username, IF(s.Broj IS NULL,0,s.Broj) as Ukupno FROM user AS c LEFT JOIN ( SELECT username, COUNT(*) AS Broj FROM odis GROUP BY username ) AS s ON c.username = s.username ORDER BY s.broj ASC, RAND()
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `odis` ADD INDEX `odis_idx_username` (`username`);
SELECT c.username, IF(s.Broj IS NULL, 0, s.Broj) AS Ukupno FROM user AS c LEFT JOIN ( SELECT odis.username, COUNT(*) AS Broj FROM odis GROUP BY odis.username ORDER BY NULL ) AS s ON c.username = s.username ORDER BY s.broj ASC, RAND()