I have a large table (500k records) and I need to select 3 random records from it. The table needs to be joined with several other tables. Even if I optimize with indexes as much as I want, I am not able to get below 0.4s and with the growing database it will only get worse.
I have read various articles on this topic, but I am not sure if those methods can be used in my specific case. The record IDs have spaces between them. Is there any method to achieve this in a row in hundredths of a second?
SELECT `s`.*, `sic`.*, `c`.*, `con`.`*, `c1`.*, `c2`.*, `c3`.*, `cur`.*, `s1`.*,
`s2`.* FROM `s`
LEFT JOIN `sic` ON sic.id = s.id
LEFT JOIN `c` ON c.id = s.cou_id
LEFT JOIN `con` ON con.id = c.con_id
LEFT JOIN `c1` ON s.col_id = c1.id
LEFT JOIN `c2` ON s.bac_id = c2.id
LEFT JOIN `c3` ON c3.id = s.col2_id
LEFT JOIN `cp` ON cp.id = s.cp_id
LEFT JOIN `cur` ON cur.id = cp.cur_id
LEFT JOIN `s1` ON s.s_width_id = s1.id
LEFT JOIN `s2` ON s.s_height_id = s2.id WHERE (s.enabled = 1) AND (s.exists = 1) AND
(s.fileEnabled = 1) ORDER BY rand() ASC LIMIT 4;
In your query you get all data for all tables. In a second step you order it by rand() and select 4 of them.
You can optimize it if getting 4 ID of table s first, and then execute the left joins:
SELECT `s`.*, `sic`.*, `c`.*, `con`.`*, `c1`.*, `c2`.*,
`c3`.*, `cur`.*, `s1`.*, `s2`.*
FROM ( SELECT id FROM s
WHERE (s.enabled = 1) AND (s.exists = 1) AND (s.fileEnabled = 1)
ORDER BY rand() ASC LIMIT 4 ) x,
JOIN `s` ON s.id = x.id
LEFT JOIN `sic` ON sic.id = s.id
LEFT JOIN `c` ON c.id = s.cou_id
LEFT JOIN `con` ON con.id = c.con_id
LEFT JOIN `c1` ON s.col_id = c1.id
LEFT JOIN `c2` ON s.bac_id = c2.id
LEFT JOIN `c3` ON c3.id = s.col2_id
LEFT JOIN `cp` ON cp.id = s.cp_id
LEFT JOIN `cur` ON cur.id = cp.cur_id
LEFT JOIN `s1` ON s.s_width_id = s1.id
LEFT JOIN `s2` ON s.s_height_id = s2.id
# Because of LEFT JOINs there may be more than 4 results.
# So a new LIMIT is needed.
ORDER BY rand() LIMIT 4