0

I have a large table and I want to select 10 random rows in the fast way.

Rows are deleted sometimes, this means i can't random between minId - maxId.

I want different rows without some order.

I tried some code that I found via Google, but this didn't help me. Now I'm using this code:

SELECT `services`.*
FROM `services`
JOIN(
    SELECT `serviceId` AS `sid`
    FROM `services`
    WHERE `services`.`catId` = '{$row["catId"]}' && `subCatId` IS NULL
    ORDER BY RAND( )
    LIMIT 10
    ) tmp
LEFT JOIN `cats` ON `services`.`catId` = `cats`.`catId`
WHERE `services`.`catId` = '{$row["catId"]}' && `subCatId` IS NULL && `services`.`serviceId` = `tmp`.`sid`
GROUP BY `services`.`serviceId`

But the page loads slowly

I saw MySQL select 10 random rows from 600K rows fast
It didn't help me.

Thanks a lot

Community
  • 1
  • 1
kfir91
  • 103
  • 1
  • 12
  • I see you already have th ORDER BY RAND(), and that's actually giving you random rows. The page speed can be many other things that just the sql query, maybe you have a lot of JS or something else loading ? – Jesper Jul 05 '15 at 08:15
  • i see this thread this is not helped me... and that i remove this Query from my page, the page load quickly the problem with the random. – kfir91 Jul 05 '15 at 08:34
  • 1
    @kfir91 Yes, ORDER BY RAND is slow. Try the approach from the 1-st answer from the link. Change your query using the pattern, given there. – user4035 Jul 05 '15 at 08:59
  • Here's [8 more](http://mysql.rjweb.org/doc.php/random) techniques; perhaps one will apply in your case. – Rick James Jul 05 '15 at 15:37

0 Answers0