Place of all tech articles

techarticles

  • Home
  • About
  • aStore
  • Contact Info
  • Tools
    • SEO Page Audit
    • SEO Keyword Rank
    • google pagerank checker
    • whois

Fastest way to select a random row from a big MySQL table

21st February, 2014 · techinf1

Fastest way to select a random row from a big MySQL table

In this article I will try to explain the “Fastest way to select a random row from a big MySQL table”. Sometimes application developer requires select a random id , rollnumber or something like that from a big MySQL table. If the MySQL table has more than 100K rows traditional SQL will consume significant execution time. But with some tricky method we can reduce execution time more than 50%.

To make this article I use the following random_data table having 500000 rows.

mysql> desc random_data;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| column1 | int(11) | YES  |     | NULL    |                |
| column2 | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

The general procedure of selecting a random row from a MySQL table is

   
mysql> select * from random_data order by rand() limit 1;
+-------+---------+---------+
| id    | column1 | column2 |
+-------+---------+---------+
| 89214 |   24675 |   44192 |
+-------+---------+---------+
1 row in set (0.34 sec)

But if you use the following SQL the query execution time will be reduced significantly

mysql> select a.* from random_data a, (select max(id)*rand() randid  from random_data) b
    -> where a.id >= b.randid limit 1;
+--------+---------+---------+
| id     | column1 | column2 |
+--------+---------+---------+
| 308029 |   37104 |   67896 |
+--------+---------+---------+
1 row in set (0.19 sec)

It needs to be mentioned that the id column would be any numeric column and it is the main focus column to make the selection.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)

Related

Posted in Database, MySQL | Tags: database, mysql, random row |
« Connect oracle database from php in Redhat/CentOS
Optimize WordPress Database by PhpMyAdmin »

Leave a Reply Cancel reply

Android App to challenge math ability
MathQuiz

Top Posts

  • Optimize MySQL table_open_cache
  • Optimize MySQL query_cache_size
  • Optimize MySQL tmp_table_size
  • Optimize MySQL innodb_buffer_pool_size
  • MySQL Replication skip error

Recent Posts

  • Recover MySQL 5.7 root Password in Redhat/CentOS 7
  • SSH login without password in Linux
  • How to write automated FTP script in Linux
  • Installing Nginx with PHP support
  • How to access HTML5 local storage

Follow US

Follow US

Categories

© Techinfobest is the house of tech articles
  • About
  • aStore
  • Contact Info
  • google pagerank checker
  • SEO Keyword Rank
  • SEO Page Audit
  • Tools
  • whois