how can we show the records randomly in php mysql?


i want to show record randomly from database.

The ORDER BY RAND() clause returns random records!
for example

SELECT product_id, title, description
FROM products
WHERE active = 1
AND stock > 0
ORDER BY RAND()
LIMIT 4

Advertisements

One thought on “how can we show the records randomly in php mysql?

  1. This works, but it’s inefficient.

    RAND() generates a random number between 0 and 1. When you ORDER BY RAND(), MySQL creates a temporary table in memory, with a temporary column to store these random numbers. As well as the overhead of having to fetch, modify and store this temporary table, no indexing will be used.

    Counter-intuitively, the best approach in this case is to use two MySQL queries. The first query is to count the number of rows (n) on the table. The second selects a row, using LIMIT and a random number between 0 and n, chosen using one of PHP’s random number functions (e.g. mt_rand).

    In this scenario, the query can be optimized internally by MySQL, with proper use of the index(es) and no temporary tables.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s