0

I have this query to display 4 random photos on a website (I have it from here (when I asked for something faster than ORDER BY RAND())):

(SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
    `users`.`username`,
    `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)

It is fast, but it seems, that one particular photo is displayed too often, about every 10th refresh of the page, or so. It has not id 1 (it has 1131), but is the 1st photo of a certain user.

Could it be, that somehow gaps in the pics table could be the reason? I have no idea what else could be the reason.

edit:

SELECT id FROM pics WHERE id < 1131 ORDER BY id DESC LIMIT 1; outputs this:

+-----+
| id  |
+-----+
| 923 |
+-----+
5
  • Yes, gaps can skew results of this method. Can you show the output of SELECT id FROM pics WHERE id < 1131 ORDER BY id DESC LIMIT 1; ? – ypercubeᵀᴹ Jun 10 '15 at 17:52
  • I have edited. it looks like a large gap. – haheute Jun 10 '15 at 18:05
  • How are records inserted into pics? Are your gaps the result in records(pictures) removed? – SQLHound Jun 10 '15 at 18:42
  • values are copied from another table after reviewing uploads. yes, the gaps are the result of deleted pics. – haheute Jun 10 '15 at 18:50
  • I don't know what to do with this. Right now I am fetching all ids from pics, cache them for 1 hour and make php shuffle and array_slice to get 4 random photos. It are about 7000 rows in pics – haheute Jun 10 '15 at 19:01
0

This has 8 'fast' ways to pick a random row from a table.

AI with gaps may be the best choice.

Or the 50/10 trick.

Add a FLOAT column may work well for you.

etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.