Thanks for the response. On 6/13/07, Robert Cummings <robert@xxxxxxxxxxxxx> wrote:
On Wed, 2007-06-13 at 10:36 -0400, Guillaume Theoret wrote: > Hi everyone, > > Can anyone recommend any good books/resources for php and mysql > performance management? I'm more interested in the software > development side (as opposed to server configuration) of things. I'm > looking for anything that would be good to know when working on > websites that get millions of hits a day. > > Also, if anyone knows of any resources/discussions that illustrate the > relative performance of joins vs multiple selects I'd love to check it > out. JOIN will almost always be faster by virtue of the query being optimized and doing the work within a single request.
Really? I thought the way it worked was that when you joined 2 tables it needed to create every row combination applicable and then apply the where clause. In large tables wouldn't this be slower? It's these kinds of optimizations and when the kick in, etc that I don't know much about. In our application we wrote an abstraction layer with lazy loading. (eg: If a User has a Profile the db users table has a profile_id and we create a ProxyProfile that only has an id and will look up its other attributes in the db if ever needed and then replace its reference by a full Profile object.) Because of this, so far the entire app only has 1 join because the other select(s) will only be done if and when they're needed. I'm certain this is faster in the average case but I wanted to know which is generally faster in case I later profile the code and see that in some cases the dependent item is pretty much always loaded.
JOIN couples two table together. JOIN simplifies the data retrieval and code. MULTIPLE SELECTS allows you to join the data yourself, possibly almost as fast as the database. MULTIPLE SELECTS allows the tables to reside in different locations. MULTIPLE SELECTS can be faster than a JOIN if your database is under heavy load and you place the tables on different servers allowing the PHP process to do the joining work. PHP processes scale horizontally better than database servers.
The db will be under heavy load (once we deploy) but we don't yet intend on distributing the database. We did however plan for it since in the scenario I described above we just need to create a different db connection for a different table. We could theoretically have as many different db servers as tables (except for that one join of 2 tables).
MULTIPLE SELECTS are usually add complexity to your code.
We dealt with this in our design.. The actual front-end functionality is all simply object-oriented programming so I can muck around as much as I want with the ORM layer without affecting any of anyone else's code. (As long as I don't change the published interface of course!)
Cheers, Rob. -- .------------------------------------------------------------. | InterJinn Application Framework - http://www.interjinn.com | :------------------------------------------------------------: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `------------------------------------------------------------'
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
