homepage Welcome to WebmasterWorld Guest from 54.205.254.108
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
improving speed of mysql driven websites
improving speed of mysql driven websites
drooh




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

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...

 

rocknbil




msg:4161760
 1:02 am on Jun 30, 2010 (gmt 0)

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.

drooh




msg:4161809
 2:20 am on Jun 30, 2010 (gmt 0)

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 :)

drooh




msg:4161811
 2:23 am on Jun 30, 2010 (gmt 0)

sorry that was actually .15 sec

whoisgregg




msg:4162333
 8:00 pm on Jun 30, 2010 (gmt 0)

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.

drooh




msg:4162491
 1:28 am on Jul 1, 2010 (gmt 0)

can u give an example and explain more on "Caching the output to a disk" ? thanks!

whoisgregg




msg:4162741
 1:20 pm on Jul 1, 2010 (gmt 0)

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;
}

Super_Chunk




msg:4162774
 2:07 pm on Jul 1, 2010 (gmt 0)

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.

netchicken1




msg:4163064
 11:00 pm on Jul 1, 2010 (gmt 0)

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.

drooh




msg:4163115
 1:19 am on Jul 2, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved