Solved PHP MYSQL Random Results

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

CilVine

Thread Starter
Joined
Apr 7, 2013
Messages
48
Hi All.

Can anybody with a little experience give me a 'heads up' on the following:

I have heard that it is better practice to shift, or move, a fair amount, if not as much, code onto the database layer as possible.

As such, I would like to know what the difference in performance would be if I removed code (that utilized the array_rand function) into the database level (by using a select statement, using 'RAND', 'ORDER BY', and 'LIMIT' instead).

I would like to think that having the database go over the table, and then return a limited , random set of results, may be quicker, and more resource-friendly, than otherwise selecting the column required, getting all the rows into the result-set, and then applying 'array_rand' to get a random set of results.

I heard that the database does use up some time, and resources, because it has to first sort the table before it picks the random rows.

So, which is better? Get random array via SQL? Or, via PHP Code?
 

JiminSA

Jim
Joined
Dec 15, 2011
Messages
3,407
Quoting from this post ...
"Logically, it's faster to do it in one step in the database vs 2 steps (database then php).
Databases are optimized to work with specific data types and process only a limited set of instructions, whereas PHP may require more resources, since it's a multi-functional interpreter.

A larger impact would be on the type of hardware used; and if it's only a small set of data, it probably wouldn't matter either way."
 

CilVine

Thread Starter
Joined
Apr 7, 2013
Messages
48
Quoting from this post ...
"Logically, it's faster to do it in one step in the database vs 2 steps (database then php).
Databases are optimized to work with specific data types and process only a limited set of instructions, whereas PHP may require more resources, since it's a multi-functional interpreter.

A larger impact would be on the type of hardware used; and if it's only a small set of data, it probably wouldn't matter either way."
Okay.

Thanks for the reply, Jim.

Did a little searching around as well. And, apparently, 'RAND' seems to work well on relatively small tables. However, large tables may need a slightly better approach.
 

JiminSA

Jim
Joined
Dec 15, 2011
Messages
3,407
In terms of efficiency on large tables, it might be best done by getting hold of the last element's index number, like so e.g. ...
Code:
SELECT * FROM MyTable ORDER BY Indexid DESC LIMIT 1
where MyTable is the name of the table in your database and Indexid is the AUTO_INCREMENT column. You would then retrieve the resulting row and put the Indexid value into $last_id.
Then you could use PHP to select random row numbers, like so e.g. ...
PHP:
$n = rand(1,$last_id);
and then retrieve the individual records. As many as you need ...
Hope that's understandable;)
 

CilVine

Thread Starter
Joined
Apr 7, 2013
Messages
48
Yep.

That is one way of going about it.

Thanks for the idea. It helps to put things into better perspective.
 

colinsp

Colin
Joined
Sep 5, 2007
Messages
2,369
Don't use SELECT * it is a slower query as not all fields are required just get what you need so just get the ID field if you are worried about performance.
 

CilVine

Thread Starter
Joined
Apr 7, 2013
Messages
48
Don't use SELECT * it is a slower query as not all fields are required just get what you need so just get the ID field if you are worried about performance.
Noted.

I don't 'SELECT ALL' anyway. Unless, of course, I need all columns returned (Something so rarely needed as to be almost non-existent).

Thanks Again.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top