Re: php / mysql performance resources

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux