Optimizing PHP/MySQL Code

kmarsh

Hey, I have a problem.

When I first started my site I wrote my code really sloppy (i.e. not using joins, always selecting * when just one field is needed, etc.) and since then I've learned to write better code. So I'm going through all my code and making optimizations (and identing.)

Now, anyone have any tips for optimizing php code for mysql?

Also, is selecting all the data from a table and running mysql_num_rows or selecting count(*) and grabbing that via. mysql_fetch_array.

Little tips like that I'm looking for to speed MySQL and PHP up.

Thanks,
-Kevin

[deleted]

First, you have to think about what you want:
superfast code, or code that you understand and that you can easily expand/change/port.

I'd allways go for the second option.

What really helps for speed is minimizing the number of queries you run.
One time "SELECT *" may be faster that two times "SELECT field"
Plus, your database server will cache queries to give you fast answers.
The fewer different queries you have, the more likely it is that you'll use a cached query.

You should only avoid "SELECT *" if you have BLOB columns that you don't really need at that time.

What really helps (but which is also quite complex) is using objects.
You'd create an object for each 'entity' in your database.
For example a 'user' object for example.
This object would then contain functions for reading the data from the table, for writing the data back to the table, and for updating the data in the table.
This does mean that all data for an entity is read/written every time, but you only need to get and write everything once, which is what counts.

kmarsh

Thanks vincent, really appreciate the tips.

I was using these kind of 'get...from...' functions to get info from the db, before I learned that mysql could do that automatically, so I'm in the process of getting rid of those things, which has sped things up a bit.

I'll have to do some reading on objects, I've been puzzled by them for quite some time, can you recommend any articles to get me started?

Anon

If your running a not so dynamic site with alot of traffic you could look into developing some code for caching variuos functions into serialized files and then pulling the info out of them when needed. Or something to the effect of slashs cron daemon.

also in code terms loop unfolding is always a nice.. i.e.

instead of:

for ($i=0;$i<20;$i++){
doSomething();
}

you could use

for ($i=0;$i<10;$i++) {
doSomething();
doSomething();
}

but that only helps if the max iter is something static and even...

and also (like vincent touched on) is use of objects and OO in your codebase... it allows for better code reuse and also you can easily come back to different parts and swap them out or upgrade as neccesary.

-Jon