Welcome to WebmasterWorld Guest from 54.197.94.141

Forum Moderators: open

Message Too Old, No Replies

improving speed of mysql driven websites

improving speed of mysql driven websites

   
12:43 am on Jun 30, 2010 (gmt 0)

5+ Year Member



my websites run from a cms system that ive created. it builds the navigation with drop downs from querying the mysql database.

im wondering if instead of having the site build the nav each time for a visitor if i should have the cms create the nav in xml or just physically write an html/php file. would this improve speed or is the mysql query cached? how does that work?

basically im just wondering if removing the querys on the front end and replacing with actual code or xml will increase speed?

im not even sure if im explain this correctly, hopefully someone will know what im talking about...
1:02 am on Jun 30, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



The mysql queries are executed each time the page is requested. PHP is not like a static page, only it's output is cached in the browser.

You'll only see a difference if your site is extremely busy, or the server goes down or slows down. I really can't say between the three which would be "faster," but most likely of all is that the difference will be marginal. Of course if you lose a connection to mysql, you lose your navigation.

I guess more of a consideration is the resources you're using. If your navigation gets set up once, and only modified every now and then, you'll be using less resources by writing out an include at the time you modify it. And I mean a static file, not a php file, just write out nav.html or nav.txt and include that via SSI for semi static pages or as a php include. Then if your site gets busy, that's one less hit on the mysql server, one less byte or two of memory PHP is using to load the nav.
2:20 am on Jun 30, 2010 (gmt 0)

5+ Year Member



thanks for the reply. right now im doing some crude benhmark testing.

place this at the top of my page
<?$time_start = microtime(true);?>

and this at the bottom
<?
$time_end = microtime(true);
echo '<hr />';
$time = $time_end - $time_start;
echo 'took: '.$time .' seconds';
?>

Then comment out out certain intensive parts of the site to see how much time they are taking. as of now my dynamic mysql/php nav is taking about .015 seconds. so, as you stated, its seems the difference is pretty marginal, just resource intensive. thanks :)
2:23 am on Jun 30, 2010 (gmt 0)

5+ Year Member



sorry that was actually .15 sec
8:00 pm on Jun 30, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Caching the output to a disk is going to be probably 10x faster than querying MySQL.

Aggressively caching all queries is worthwhile for any site since Google incorporates page load time into their ranking algorithm.
1:28 am on Jul 1, 2010 (gmt 0)

5+ Year Member



can u give an example and explain more on "Caching the output to a disk" ? thanks!
1:20 pm on Jul 1, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Basically, you can do something like this to cache your navigation for 24 hours at a time:

$path_to_cache = './navigation.html';
if(file_exists($path_to_cache) && filemtime($path_to_cache) > strtotime("-24 hours")){
include($path_to_cache);
} else {
// query MySQL
// generate navigation HTML
file_put_contents($path_to_cache, $nav_html);
echo $nav_html;
}
2:07 pm on Jul 1, 2010 (gmt 0)

5+ Year Member



I use a similar caching method to whoisgregg for the main navigation on our e-commerce site - only I store the HTML in the database instead of writing to file and the cache expires every hour but esentially it's the same thing. Using this method I run one quick query to grab the cache and only rebuild the HTML (which requires alot more resources) once an hour. On a high volume traffic site this can take alot of load off of the server.
11:00 pm on Jul 1, 2010 (gmt 0)

10+ Year Member



Firebug in firefox has a page speed indicator, it was recommend by Google. My pages are around 85/100 up from mid 70's when I followed their recommendations.

looks like a good tool that you can use, and that Google must take notice of.
1:19 am on Jul 2, 2010 (gmt 0)

5+ Year Member



@netchicken1 what does the 70 mean? is that the speed?
 

Featured Threads

Hot Threads This Week

Hot Threads This Month