Where to find Best Practices?

for MySQL PHP applications

[lorax]

In the thread [webmasterworld.com] Andreas notes that creating a temporary table with the results of a query and then querying that for a final result set is quicker than taking the result set of the initial query and sifting through it with PHP. Technically either would work. Speed is the issue as are memory and cpu considerations.

Database normalization (using the 3 Normal Forms) is an obvious must do. But what about those little tidbits of knowledge like the example above? I for one question when I should use sessions versus when it's better to pass the necessary info on the URL or via cookie. Again, technically they all work, but which one should be used, and when - and why?

So I'm wondering if you might have some resources you'd be willing to share where I (or anyone else who frets over this stuff) could go to learn more about these subtle issues.

[dingman]

The first thing that crosses my mind is to search through this forum. (suprise) Andreas has written up a number of benchmarks, on subjects from string interpolation to database interaction, and quite often threads here touch on best practices for particular tasks.

Specifically to MySQL, I can't help a whole lot because I don't use it all that often. I prefer PostgreSQL, and since I run my own server I get what I want most of the time. :)

[andreasfriedrich]

I donīt know of any other resources either. I guess itīs just a matter of experience and probably most of all analyzing and understanding your problem and how you might solve it. Ask yourself what are the parts of your application that will run most often, for the longest time, take the longest time, are most prone to tweaking.

Common sense will help as well. A RDBMS was written to handle large sets of data. Script languages were written to be flexible and allow for very high level instructions. So let each part of your application do what itīs best at. Use EXPLAIN [mysql.com] to analyze and optimize your SELECT [mysql.com] queries. Use indizes. Normalize your databases. Donīt do it when redundant data will be faster and speed matters. But know that this is as exception to the general rule that normalized dbs will be more efficient both speed and storage wise.

If you really must: Optimize Apache [httpd.apache.org] and Mysql. Play around with different settings. Optimize the server. Buy more memory, a faster hard drive. Think about whatīs cheaper and has a better ROI, spending a lot of time speezing the last bit of speed out of your code or investing in a bit more memory.

One way to optimize MySQL [mysql.com] might be not to use it at all and go with a Berkeley DB instead. That`ll save you quite some overhead and will work well up to certain level of complexity and number of records. Simple relational database structures can be build using Berkeley DBs quite nicely.

Does optimization really matters? It will depend on you application.

Just some ideas ;)

Andreas

[lorax]

Hey Guys,
Thanks for the responses. I've pretty much read through all of Andreas' posts gleaning what I could. ;)

I've come to the conclusion that it pretty much comes down to basic understanding of concepts combined with a lot of experience. Tidbits seem to be all over the place but each applies to specific situations and no one answer fits all. In short - there are no silver bullets.

So - it seems I may be asking some more specific questions as I analyze the two apps I'm working with now.