Solutions to Improve Efficiencies of Random Select in MySQL - Techist - Tech Forum

Go Back   Techist - Tech Forum > Computer Software > Programming
Click Here to Login
Reply
 
Thread Tools Display Modes
 
Old 02-24-2018, 03:12 AM   #1 (permalink)
Virgin Techie
 
Join Date: Oct 2017
Location: Qingdao
Posts: 3
Wink Solutions to Improve Efficiencies of Random Select in MySQL

Solution 1 - just Order By Rand()

SELECT * FROM user ORDER BY rand() LIMIT 10 This solution is not recommended, because the more the data is, the slower it is.


Solution 2 - Use JOIN method one by one, and do it 10 rounds.


SELECT * FROM `user` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `user `)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1 The performance will be improved by times, but the I/O is big due to many select queries.


Solution 3 - Use SQL statement to random select the ID order, and then use IN to select.


Select the maximum and the minimun IDs first.
Code:
SELECT MAX(id),MIN(id) FROM user
Then generate values randomly.
Code:
$numbers = range ($min,$max);  //shuffle shufffle the array shuffle($numbers); //array_slice Choose a slice of the array which is bigger than the number we need to select, in case there is any ID that does not exist. $result = array_slice($numbers,0,20);
Then select
Code:
select * from user where id in ($ids) order by field('id,'.$ids) LIMIT 0,10
Problem solved!
__________________
reneet is offline   Reply With Quote
Reply

Tags
mysql, random select

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Solutions to Improve Efficiencies of Random Select in MySQL reneet Linux and Open Source 0 10-30-2017 10:20 PM
Select All Fixes Standard Windows Select All Text Behavior Osiris Tips, Tricks & Tutorials 0 03-23-2010 07:09 AM
MySQL Database Comparison Software Mysql Structure Compare Osiris Tips, Tricks & Tutorials 0 12-22-2009 10:59 AM
PHP Mysql Select all of 1 Column, name variables with another column lemonshindig Web Graphics and Design 1 08-28-2005 01:34 PM
Bypass OS select screen during boot?!?! emjlr3 Microsoft Windows and Software 3 08-15-2003 06:24 AM



Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -5. The time now is 01:13 PM.


Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2018, vBulletin Solutions, Inc.