PHP Database Optimization

[barrywolf]

Hi guys,

I'm currently using a MySQL database with PHP code to access the data from it. I'm finding it to be a bit slow - I currently use it for my blog as well as a small forum.

Could it be that I am pulling too much data at one time? Are there any good tips for php coding - I'm quite new to the whole PHP-Database scene and would love to have a faster system.

 

[Forbairt]

How many queries are you hitting the database with per page load ?

In the past I've found a lot of people doing very silly things like individual queries when things could be grouped into one query

For example if you're loading your blog posts .. don't create 1 query per blog post if you were displaying 10 blog posts for example select the 10 most recent

Have you thought about creating some simple caching on your site ? so that only every 15 - 30 minutes you need to be hitting the database should mean pretty much instant load times

 

[barrywolf]

I have tried to group my queries and it did have a great performance boost than when I had everything done individually. This caching idea sounds very good - I only really update the content every day so caching it sounds like a viable solution. My only problem with caching the data is that its a bit of pain when developing - but thats just me being fussy! Thanks Forbairt, you've given me something to ponder about!

 

[Forbairt]

on your dev site try figuring out which queries are taking the most time and why .. could be you're doing a silly join which is resulting in huge amount of DB caching / writing to disk temp files .. I know I've killed servers in the past with some interesting queries

 

[louie]

You can also try adding an INDEX to the tables that has most hits.

 

[php.allstar]

Hi,

First and foremost, what you need to do is pinpoint where the problem is.

To identify whether your queries are the problem, google for how to enable the "Slow Query Log" for your setup. This will present you with a list of queries that are taking over the "Long Query Time" setting. If you are a really clever programmer and big into security and using prepared statements you'll want to have a look at the general query log.

Now that you have identified the query that is causing the problem, (and as Louis has correctly advised!) see if there are indexes added to the fields you have used for joins and where statements.

If you're a newcomer to this, it may all seem gobbledeegook, but trust me, this will lead you to your problem.

There are lots of stuff I could ask you about your setup but I think it may be way over your head...

A couple of things I've found to work quite well:

Use persistent database connections.
Try to avoid "many to many" relationships.
Use sub queries.

Too much to explain but if you google them you'll be well on your way!

 

[barrywolf]

Thanks fellow php guru's I've been looking into persistant connections and caching as well as the indexing - it's all very advanced php, which I'm not terribly used to but I appreciate the heads up on which diretion I should be heading.