CODEWITHSUNDEEP
phpperformanceoptimizationram
antonin.lebrard
antonin.lebrard

Reputation: 139

How's the performance in php with a request sql with LIMIT in terms of RAM and time of execution

I'm writing a PHP script but I'm facing a little question, What the concrete difference between : 

$sql = "SELECT * FROM $table ORDER BY date DESC LIMIT $limit OFFSET $offset";
$result = mysql_query($sql) or die(mysql_error());

and : 

$sql = "SELECT * FROM $table ORDER BY date DESC";
$result = mysql_query($sql) or die(mysql_error());

In terms of RAM and time of execution used in the server ?

Edit: Actually I was opting for the first sql request, but for a precise need, I have to recheck a certain amount of data with my PHP script to build another amount of data (smaller) but I cannot do this with sql pure. (I use fetch_array in all the result until I have the amount I want) So I want to know (before I do anything wrong), what solution is faster for the client (sql + php) and what solution is the more safier in terms of load into the server ?

Edit2 :Re-paste the ORDER BY clause

Upvotes: 0

Views: 131

Answers (2)

Healkiss
Healkiss

Reputation: 367

Unfortunately, that's does not change anything in me most common cases.

Excellent response on which cases are improved : Does limiting a query to one record improve performance

And for your case, this article is about offest/limit performances : http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

Upvotes: 1

arkascha
arkascha

Reputation: 42895

Well, isn't the answer obvious?

If you limit the result set of a select query, then the amount of data inside that result set is reduced. The result of this, when looking at phps memory usage depends mostly on one central thing:

  1. if you retrieve all of the result set in a single go, for example by using something like fetchAll(), then the whole result set is read into memory.

  2. if you process the result set in a sequential order, then only one element of a result set is read into memory at a given time. Thus, unless you copy that, the memory footprint is limited.

Another thing is performance inside php: obviously the more elements in the result set you process, the more load you generate. So limiting the size of the result set will probably reduce load, unless you have a good way to pick only a certain range of elements to process from the result set.

As a general rule of thumb: always only retrieve the data you really require and want to process.

Upvotes: 0

Related Questions