PHP/MySQL Efficiency Theory

What considerations are important?

[Nick_W]

Hi all,

I have an app with many tables and a class that handles the basic (add,edit,delete,update) functionality required to interact with each table. - Several different, very simple classes, one class per table.

Say a page has 50 'entries' - each requiring some data from each table.

Is it best to keep the classes as they are: Light and simple, keeping the 'baggage' the app carries from page to page light.

Or, extend the classes to carry much more sophisticated functionality.

The first means many, many calls to the DB for one page with 50 entries. But, much less 'baggage' being carried from page to page throughout the site. (size of require()'d classes/scripts)

The second, more baggage but more complicated calls to the DB meaning much less calls to the DB per page and more efficient queries.

Which is the lesser of the 2 evils and why?

I'd go with the second but, what about the huge amount of code being carried from page to page?

Look forward to your thoughts...

Nick

[andreasfriedrich]

Let MySQL [mysql.com] handle as much of the data stuff as you can. It was written to handle large data sets efficiently. Your goal should be to let MySQL [mysql.com] serve the data to be displayed on the page in a way that requires the least processing by PHP [php.net]. Usually this should make for rather lightweight PHP [php.net] classes as well. It will increase the maintainability of your code as well. In a web application I´d be much more interested in writing maintainable and secure code than squeezing the last bit of speed out of the code.

Avoid code bloat but know that doing what´s necessary to be done in PHP [php.net] is not code bloat. If "compile" time of your scripts is really an issue I´d use the Zend Optimizer to have it "precompiled". If speed is of utmost importance you should stay away from very high level languages like PHP [php.net] and Perl [perl.com] anyway and use C.

Having said that I´d go with the implementation of a class that has all the functionality that the class should provide from a locigal POV. If you already use a very high level language keep you classes at that very high and abstract level as well.

Andreas

[brotherhood of LAN]

quick q about mysql while we are on the subject ;)

Is there a way to tweak memory usage for it or is that dealt with inside the code? I bumped my localhost PHP up to 96Mb and I never see mySQL go above 4Mb

//addded

cheers andreas ;)

[edited by: brotherhood_of_LAN at 1:19 pm (utc) on Mar. 21, 2003]

[Nick_W]

So, to summarize:

More complex classes and queries would be better than 10's of calls to the DB per page Andreas?

Nick

[andreasfriedrich]

You can tell the mysqld in your

my.cnf

file how much memory it may use for certain things. Other than that MySQL [mysql.com] will handle memory usage itself.

Have a look at 5.5.4 How MySQL Uses Memory [mysql.com] for some infos on how to tweak it.

Andreas

[andreasfriedrich]

>>More complex classes and queries would be better than
>>10's of calls to the DB per page Andreas?

Now that is such a question that you will not find a single lawyer that would answer with a simple yes or no.

So let me answer in a way a lawyer would: Generally yes. You do realize though that more complex and 10's of calls allow for a wide variety of situations. If the classes look complete/provide the needed functionality only in a more complex way than that´s the way I´d go.

Andreas

[Nick_W]

Thanks!

It's just blog stuff Andreas.

member table, blog table, articles table etc.

The 'many calls' comes from getting the member names, blog entries, links to member bios all from seperate tables (maybe 10posts per page).

Just wanted some thoughts, so thanks again ;)

Maybe it would be good to write anoher class specifically for getting blog pages? - then use the more simple classes for member input, validation, logg ins etc...

Nick

[andreasfriedrich]

I´d consider writing a rather general posting class which provides functionality to draw a form, accept and validate input, write and read it from the database, etc. For blogging purposes you could use a blogging class which inherits the general features from the posting class and adds only the blogging specific stuff.

A separate Authentication class would be a good idea as well.

Andreas

[Nick_W]

>A separate Authentication class would be a good idea as well.

Yes, contained in the member class at the moment along with edit/add/delete/update

The blog stuff, as well as the articles stuff will each have seperate classes that can be used along-side the member class...

Nick