Choosing a random row fairly on MySQL

I have a MySQL table classifieds, it has the following fields:

• id = Primary Key, autoincrement
• title, desc, ........., other fields,
• featured = int(1) (1 or 0) but can be changed to true/false,
• featured_start(date), featured_end(date)

开发者_JS百科

I want to show just one classified at a time on my homepage. I mean, it doesn't rotate dynamically with AJAX, but with each page refresh or reload (F5).

Could someone give me some idea about how to code a fair MySQL query to get one featured classified at a time? By fair, that one classified must not show too many more times than other, and of course featured_end must be taken into account for not showing expired classifieds.

---

ORDER BY RAND() LIMIT 1 is the classic dirt-simple way of grabbing a random row. However, it does not scale well with lots of rows in your table - it will have to run the RAND() function for every single row in your table, and then order all those rows (showing you just the first).

I would recommend reading this useful article.

The tl;dr version: the best database-side solution will most likely be something like:

SELECT `your_table`.*
FROM `your_table`
JOIN
(
  SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `your_table`)) AS `id`
) AS `random_row` USING(`id`)

Seriously, give that article a read, it's the best response to your question that I think you will find.

To people who would bring up the fact that gaps in your primary key will cause inaccuracies: yes, he covers that in the post I linked.

---

To take featured_end into account add WHERE featured_end >= CURRENT_DATE, ie

SELECT * 
FROM classifieds
WHERE (featured_end >= CURRENT_DATE)AND(featured = 1)
ORDER BY RAND()
LIMIT 1

---

You could try this, using the 'RAND' function -

SELECT classifieds.* 
FROM classifieds
ORDER BY RAND()
LIMIT 1