Alternative for Rand function in MySQL-CodePudding

Initially, the query was fast but as the rows increased the performance degraded. My table consists of 600K rows and now we face a major performance issue with the Rand() function. Any help would be much appreciated. Sorry if it's a duplicate question

SELECT id,postalCode,location 
from jobs_feed 
WHERE check_parsed= 1 AND similarjob_status=0 AND is_updated != 0
ORDER BY RAND() LIMIT 1;

CodePudding user response：

MySQL needs to determine a random number of all 600K rows, just to determine which row comes first.

A possible work around can be:

SELECT id,postalCode,location
FROM (
   SELECT id,postalCode,location 
   from jobs_feed 
   WHERE check_parsed= 1 AND similarjob_status=0 AND is_updated != 0
   ORDER BY id LIMIT 1000 )x
ORDER BY RAND() LIMIT 1;

This will first select the first 1000 records, and then pick a random row from those.

NOTE: This behavior is different from that you where doing, but faster because only the first 1000 records need to be fetched.

NOTE2: You can change 1000 to another (bigger) value, as long as you think performance is OK (and this alternative way of selecting is OK.)

CodePudding user response：

There isn't much way to optimize ORDER BY RAND() LIMIT 1. But we can try adding the following index to speed up the WHERE filter:

CREATE INDEX idx ON jobs_feed (check_parsed, similarjob_status, is_updated);

This index, if used, can potentially allow MySQL to discard many records from the query pipeline, leaving behind only a small subset to randomly shuffle.