Server Load Question: PHP vs mySQL

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
ColonelSandersLite
Forum Commoner
Posts: 35
Joined: Sun May 09, 2010 1:32 am

Server Load Question: PHP vs mySQL

Post by ColonelSandersLite »

Mostly, I just want to confirm my thinking here and hear other points of view.

Let's suppose that you're building a project and you end up in a situation where you can *either*:

A: Request all the data you might need from a table or two and process it with php to send to the user only the data he needs.
B: Use a lot of queries to get the data you need, which will dramatically reduce the amount of processing you have to do in php and reduce the dataset you're working with dramatically.



In my thinking, the choice is really determined by the size of the dataset, and more specifically, how long it takes to return the data. If the total dataset is small and can be returned very quickly, it is better to use php for this, but if it is very large, it can become most desireable to limit the returned dataset as it will take longer and longer to return the whole thing.


As a hypothetical, let's suppose you're coding an index page for articles written by a small group of site administrators. You need to get all the authors from one table, and the article titles, dates, etc from another.

In this index page, you want to group the articles A: acording to date, B: by author, c: by title.

So pseudocode for method A:
[text]
query to get all the authors and load them into an array
query to get all the relevant article bits and load them into an array

Sort/copy Arrays to dump a list of the 10 most recent articles by any author

foreach author
Sort/copy arrays to dump a list of of the 10 most recent articles by a specific author

sort/copy arrays to dump a list of the first 10 articles organised by title
[/text]


So pseudocode for method B:
[text]
query to get the 10 most recent articles
query to get their authors
output the data

query the authors
foreach author
query to get the 10 most recent articles by a specific author
output the data

query to get a list of the first 10 articles organised by title
output the data
[/text]


By my thinking:
If we're talking about 5 (or so) authors writing a total of an article every other day (or about 180 articles per year total) method A is probably better.
If we're talking about a large community writing maybe a total of 20 articles a day (like a forum, with each post being an "article") method B is better.

Though at some point, you need to break it up a bit. Like in case B, it's probably OK to return the last 50 articles in a single query, and in case A, limit the query to articles written in the past year. We could build on this and make it horribly complicated, but I'm *really* trying to keep it simple.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Server Load Question: PHP vs mySQL

Post by JakeJ »

In almost all cases, you want to use the tool best suited for the job.

PHP is obviously capable of handling large amounts of data processing but it's ideally suited to returning a specific data set in a particular format and being served to the client.

You're better off using MySQL to do what it was designed for which is handle data input and output as quickly as possible.

Besides that, if you're spending resources processing large amounts of data that could be handled by the database, it has less resources available to be processing and serving web pages.

It's a fair bet that mysql can return many smaller queries faster than PHP can parse out larger data sets to return only the records needed.

In any case, it's probably a moot point. If you're at the state when processing power is so critical you have to worry about the difference between the two, it's time to start doing some load balancing. Splitting functions off to different servers, etc.

Even so, use the tool for what the tool was designed for.

MySQL is good at math functions too, but you wouldn't pump data in to the database so you could do calculations would you?
User avatar
ColonelSandersLite
Forum Commoner
Posts: 35
Joined: Sun May 09, 2010 1:32 am

Re: Server Load Question: PHP vs mySQL

Post by ColonelSandersLite »

Your logic, in my opinion contradicts itself.

The tools in this case are:
MYSQL (or really any sql db system)
PHP
HTML/CSS/FLASH/JAVA/Whatever


The tools are engineered towards:
MySQL - Storing and retrieving data
PHP - Processing data for display/storage
HTML/CSS/FLASH/JAVA/Whatever - Displaying the data to the user

I would posit, that so long as the dataset isn't *too big*, you would want to do the processing in php for the same reason you don't "pump data in to the database so you could do calculations".


Multiserver environments aren't a consideration here (maybe I should have specified earlier, I thought it was self explanatory though) as this question answers itself. If the purpose is to balance the load between servers, you do more processing on whichever server is more idle and less on the busier server, taking into account the time it takes for the servers to send the data to eachother.

In this case, I'm mostly interested in single server performance optimization for the sake of speed from the end user's point of view. I have not seen any actual data or test results of getting things done in php vs mySQL as stated in the original question and have always just worked under assumptions. I tried googling it, but the way google searches work, those keywords turned up completely irrelevant subjects. If anyone can direct me to further reading on the subject, it would certainly be most appreciated.
Post Reply