[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can you improve this code for performance? (mysql/php)

Posted on 2005-04-06
2
Medium Priority
?
231 Views
Last Modified: 2013-12-12
Is there a more efficient means to query new posts, like my code below? It works well, but is a bit harsh on the CPU.




$i = 0;
$get_items = "SELECT b.blog_id, b.author_id, b.date, b.subject, b.views, u.user_id, u.username, u.valid, u.category ";
$get_items .= "FROM nlb3_blogs AS b LEFT JOIN nlb3_users AS u ON b.author_id = u.user_id ";
$get_items .= "WHERE b.author_id = u.user_id AND b.access != 4 AND u.category != 0 ORDER BY b.date DESC LIMIT 50";
$get_table_data = mysql_query($get_items) or die("MySQL Error #".mysql_errno().": ".mysql_error()."\nQuery:");

      if (mysql_num_rows($get_table_data)<1) {

      $ets->recent_blogs2 = 'No New Blogs';

} else {
            while ($row = mysql_fetch_array($get_table_data)) {
            stripslashes_array( $row );
            $ets->recent_blogs2[$i]->username       = stripslashes($row['username']);
            $ets->recent_blogs2[$i]->user_id      = $row['user_id'];
            $ets->recent_blogs2[$i]->subject      = stripslashes($row['subject']);
            $ets->recent_blogs2[$i]->views            = $row['views'];
            $ets->recent_blogs2[$i]->category     = $row['category'];
            $ets->recent_blogs2[$i]->blog_id     = $row['blog_id'];
            $ets->recent_blogs2[$i]->date     = date("F j, Y, g:i a", $row['date']);
            $i++;
            }
            
      }
0
Comment
Question by:jeffparis
2 Comments
 
LVL 1

Accepted Solution

by:
AEG-IT earned 1500 total points
ID: 13721135
First thing to try is put your query on one line or actually I should say one statement, this way the interpreter does not have to preform as many instrucitons:

$get_items = "SELECT b.blog_id, b.author_id, b.date, b.subject, b.views, u.user_id, u.username, u.valid, u.category
                     FROM nlb3_blogs AS b LEFT JOIN nlb3_users AS u ON b.author_id = u.user_id
                     WHERE b.author_id = u.user_id AND b.access != 4 AND u.category != 0 ORDER BY b.date DESC LIMIT 50";

Secondly you can look at assigning your object all at once....

while ($ets->recent_blogs2[] = mysql_fetch_object($get_table_data)) {
     $i = count($ets->recent_blogs2) - 1;
     $ets->recent_blogs2[$i]->username = stripslashes($ets->recent_blogs2[$i]->username);
     $ets->recent_blogs2[$i]->subject = stripslashes($ets->recent_blogs2[$i]->subject);
     $ets->recent_blogs2[$i]->date = date("F j, Y, g:i a", $ets->recent_blogs2[$i]->date );
}

This does optimize your code but I dont beleive you will be able to reach the results you are looking for, but just optimizing the code.  I suspect that 80 - 90 of your time of execution is in MySQL.

Hope this helps

Brad
0
 
LVL 1

Author Comment

by:jeffparis
ID: 13722421
Actually, that does help, thanks. I have brain fry right now and could not think of any optimizations. Even just a 5% improvement is worth it:)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question