Welcome Guest!

If you do not have an account yet on The Web Squeeze forums, please Register! It’s FREE and there are many benefits:

  • Receive Fast Advice
  • Learn Programming Languages
  • Get Professional Website Reviews
  • Quick Troubleshooting Assistance

> Tips To Improve Your Site Performance

This is a discussion on Tips To Improve Your Site Performance, within the MySQL section. This forum and the thread "Tips To Improve Your Site Performance" are both part of the Programming Your Website category.

 
 Reply to this topicStart new topic
> Tips To Improve Your Site Performance
Monie
post Jun 3 2008, 04:42 AM
Post #1


Squeeze Machine
*****

Posts: 731
Joined: 13-February 08
From: Borneo
Hey everyone, good day to you all...!

I found this tips "Tips to improve your site performance" and i couldnt understand this line where it says "Data manipulation from Recordsets should be done at the query level itself. Do not perform sorting, ordering etc. in the script."

Can someone explain the meaning biggrin.gif
Thanks.


--------------------
Go to the top of the page
 
+Quote Post
Rakuli
post Jun 3 2008, 05:06 AM
Post #2


Squeeze Machine
Group Icon

Posts: 760
Joined: 13-February 08
From: Catching the squeezed drips downunder.
Generally speaking, MySQL is much faster than the scripting language you are querying from. It is built and optimised with functions that let you manipulate data from the MySQL server itself using SQL.

If I were to build an application with PHP that required me to retrieve the full name of users from a database (first and last names concatenated) and also retrieve a sum of the users assets that were found in another table (house, car, pets) the optimal way to achieve this would not be to get the data from MySQL and manipulate it with PHP. The most resource economical way to complete the task would be to do all of the data manipulation within the query. This way, PHP gets the data in its ready to use form after MySQL has performed the required functions.

CODE
SELECT CONCAT(u.first_name, ' ', u.last_name) AS full_name, SUM(ua.asset_price) AS total_assets FROM Users AS u
LEFT JOIN user_assets AS ua ON (u.id=ua.user_id)
GROUP BY u.id ORDER BY total_assets DESC


In a nutshell, dont't be afraid of making MySQL do a lot of work for you, it is faster and more suited to the task.

A more fundamental way of looking at it is the SQL ORDER BY clause. When fetching a recordset from MySQL, you add the ORDER BY clause so the data comes out in the order you desire. You do not get all of the data and then run an expensive, resource-hogging, array sorting function.


--------------------
Luke Dingle . com

Turn Over a Playful Leaf on Web Design -- read about the javascript cat
Go to the top of the page
 
+Quote Post
holger
post Jun 3 2008, 05:29 AM
Post #3


Fresh Squeezed
**

Posts: 30
Joined: 6-March 08
From: Birmingham
One other example is a query to find the number of records that match a certain criterion. I work a lot with open source code and I often come across something like this
CODE
$result = mysql_query("SELECT somefield FROM sometable WHERE otherfield='x'");

$count = 0;
while ($row = mysql_fetch_row($result)) $count++;
return $count;

This is a very expensive way of doing the task. The following alternative is a lot faster
CODE
   $result = mysql_query("SELECT COUNT(*) FROM sometable WHERE otherfield='x'");
  
   $row = mysql_fetch_row($result);
   $count = $row[0];
   return $count;


Note: For simplicity I have left out any checking of the success of the query.


--------------------
Viventic Web Design and Application Development
Go to the top of the page
 
+Quote Post
Monie
post Jun 3 2008, 05:43 AM
Post #4


Squeeze Machine
*****

Posts: 731
Joined: 13-February 08
From: Borneo
Oh now I know!
Thanks mate! That is one perfect explanation!


--------------------
Go to the top of the page
 
+Quote Post
« Next Oldest · MySQL · Next Newest »
 
If you found The Web Squeeze to be helpful, please donate so we can keep this site FREE, FRESH, and fortified with Web Design & Development info!
 Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Collapse

> Similar Topics

    Topic Title Replies Topic Starter Views Last Action
No New Posts 5 alex 426 5th March 2008 - 04:45 PM
Last post by: rewake
No New Posts   9 rickc 892 11th September 2008 - 09:21 AM
Last post by: rich97
No New Posts 10 Sean 894 19th June 2008 - 08:11 PM
Last post by: mv08jml
No New Posts   8 AboutAutism 982 23rd February 2008 - 10:30 AM
Last post by: 1christopher
No New Posts   7 rewake 767 15th February 2008 - 05:06 PM
Last post by: Linda