PHP/MySQL Optimizations?

**eikainu** · 06-23-2010 09:39 PM

Anyone care to share their views on optimization techniques for scripting PHP/MySQL to reduce resource usage? :]

I know free hosts tend to have a strict policy on the amount of resources you are using, so this would be some ideal information for the average user.

I've heard about stored procedures but I'm not exactly sure what that is.

**misson** · 06-24-2010 12:03 AM

A good grounding in programming and DB fundamentals is the best way of minimizing resource usage. If you're asking for tutorials and articles (which you'd better be, given the forum), start with § 7.2.1 of the MySQL manual: "Optimizing Queries with EXPLAIN". Also read up on database normalization, though knowing when to denormalize (hint: after normalizing & profiling) is also important.

As for PHP, study algorithm design (the closest I could come to a tutorial is Paul E. Dunn's overview of algorithm design). An essential part of this is algorithm complexity, including Big-O notation (the primary measure of algorithmic complexity in time & space, that is, how the time & space needs of an algorithm increase as the problem size increases).

Web searches will turn up plenty of other articles on each of these topics; don't stick to the above links. Be careful of what you find, as some may be out of date or just plain wrong (both more true of DB articles than algorithm design). Note that these topics are complex; algorithms in particular isn't well covered by reading a few tutorials. Books are your best bet, but this isn't the forum for that and the topic has been well-covered elsewhere. There are a few threads in programming help and quite a few questions on Stack Overflow on these topics. I don't recommend asking your question on SO as-is, since it's far too general and related questions have been covered many times before.

Stored procedures are simply procedures stored on and executed by the SQL server but written by a DB user or admin. You can also have stored functions; together, they are called stored routines. Read the MySQL manual, section 19.2 "Stored Routines" to start.