How important is caching for a site's speed with PHP?

I've just made a user-content orientated website.

It is done in PHP, MySQL and jQuery's AJAX. At the moment there is only a dozen or so submissions and already I can feel it lagging slightly when it goes to a new page (therefore running a new MySQL query)

Is it most important for me to try and optimise my MySQL queries (by prepared statements) or is it worth in looking at CDN's (Amazon S3) and caching (much like the WordPress plugin WP Super Cache) static HTML files when there hasn't been开发者_开发百科 new content submitted.

Which route is the most beneficial, for me as a developer, to take, ie. where am I better off concentrating my efforts to speed up the site?

Premature optimization is the root of all evil

-Donald Knuth

Optimize when you see issues, don't jump to conclusions and waste time optimizing what you think might be the issue.

Besides, I think you have more important things to work out on the site (like being able to cast multiple votes on the same question) before worrying about a caching layer.

Its done in PHP, MySQL and jQuery's AJAX, at the moment there is only a dozen or so submissions and already i can feel it lagging slightly when it goes to a new page (therefore running a new mysql query)

"Can feel it lagging slightly" – Don't feel it, know it. Run benchmarks and time your queries. Are you running queries effectively? Is the database setup with the right indexes and keys?

That being said...

CDN's

A CDN works great for serving static content. CSS, JavaScript, images, etc. This can speed up the loading of the page by minimizing the time it takes to request all the resources. It will not fix bad query practice.

Content Caching

The easiest way to implement content caching is with something like Varnish. Basically sits in front of your site and re-serves content that hasn't been updated. Minimally intrusive and easy to setup while being amazingly effective.

Database

Is it most important for me to try and optimise my MySQL queries (by prepared statements)

Why the hell aren't you already using prepared statements? If you're doing raw SQL queries always use prepared statements unless you absolutely trust the content in the queries. Given a user content based site I don't think you can safely say that. If you notice query times running high then take a look at the database schema, the queries you are running per-page, and the amount of content you have. With a few dozen entries you should not be noticing any issue even with the worst queries.

I checked out your site and it seems a bit sluggish to me as well, although it's not 100% clear it's the database.

A good first step here is to start on the outside and work your way in. So use something like Firebug (for Firefox), that - like similar plug-ins of its type - will allow you to break down where the time goes in loading a page.

http://getfirebug.com/

Second, per your comment above, do start using PreparedStatements where applicable; it can make a big difference.

Third, make sure your DB work is minimally complete - that means make sure you have indexes in the right place. It can be useful here to run the types of queries you get on your site and where the time goes. Explaining plans

http://dev.mysql.com/doc/refman/5.0/en/explain.html

and MySQL driver logging (if your driver supports it) can be helpful here.

If the site is still slow and you've narrowed it to use of the database, my suggestion is to do a simple optimization at first. Caching DB data, if feasible, is likely to give you a pretty big bang for the buck here. One very simple solution towards that end, especially given the stack you mention above, is to use Memcached:

http://memcached.org/

After injecting that into your stack, measure your performance + scalability and only pursue more advanced technologies if you really need to. I think you'll find that simple load balancing, caching, and a few instances of your service will go pretty far in addressing basic performance + scalability goals.

In parallel, I suggest coming up with a methodology to measure this more regularly and accurately. For example, decide how you will actually do automated latency measures and load testing, etc.

For me - optimising DB is on first place - because any caching can cause that when you find some problem , you need to rebuild all cache

There are several areas that can be optimized.

1. Server
2. CSS/JS/Images
3. PHP Code/Setup
4. mySQL Code/Setup

1st, I would use firefox, and the yslow tag, to evaluate your website's performance, and it will give server based suggestions.

Another solution, I have used is this addon.

http://aciddrop.com/php-speedy/

"PHP Speedy is a script that you can install on your web server to automatically speed up the download time of your web pages."

2nd, I would create a static domain name like static.yourdomainane.com, in a different folder, and move all your images, css, js there. Then point all your code to that domain, and then tweak your web server settings to cache all those files.

3rd, I would look at articles/techniques like this, http://www.catswhocode.com/blog/3-ways-to-compress-css-files-using-php to help compress/optimize your static files like css/js.

4th, review all your images, and their sizes, and make sure they are fully optimized. Or, convert to using css sprites.

http://www.smashingmagazine.com/2009/04/27/the-mystery-of-css-sprites-techniques-tools-and-tutorials/ http://css-tricks.com/css-sprites/

Basically for all your main site images, move them into 1 css sprite, then change your css, to refer to different spots on that sprite to display the image needed.

5th, Review your content pages, which pages, change frequently, and which ones rarely change, and those that rarely change, make those into static html pages. Those that change frequently, you can either leave as php pages, or create a cron or scheduled task using php command line to create new static html versions of the php page.

6th, for mySQL, I recommend you have the slow query log on, to help identify slow queries. Review your table structure, make sure they are optimal, and have tables, that are well designed. Use views and stored procedures, to move hard sql logic or functioning from php to mySQL.

I know this is a lot, but I hope it's useful.

It depends where your slowdowns really lie. You have a lot of twitter and facebook stuff on there that could easily slow your page down significantly.

Use firebug to see if anything is being downloaded during your perceived slow loading times. You can also download the YSlow firefox plugin to give you tips on speeding up page loads.

A significant portion of perceived slowness can be due to the javascript on the page rather than your back-end. With such a small site you should not see any performance issues on the back end until you have thousands of submissions.

Is it most important for me to try and optimise my MySQL queries (by prepared statements)

Sure.
But prepared statements has nothing to do with optimizations.

Nearly 99% of sites are running with no cache at all. So, I don't think you're really need it.
If your site is running slow, you have to profile it first and then optimise certain place that proven being a bottleneck.