tags:

views:

261

answers:

5

I have a page that will pull many headlines from multiple categories based off a category id.

I'm wondering if it makes more sense to pull all the headlines and then sort them out via PHP if/ifelse statements or it is better to run multiple queries that each contain the headlines from each category.

A: 

It's usually better not to overload the DB, because you might cause a bottleneck if you have many simultaneous queries.

However, handling your processing in PHP is usually better, as Apache will fork threads as it needs to handle multiple requests.

As usual, it all comes down to: "How much traffic is there?"

Seb
+8  A: 

Why not do it in one query? Something like:

SELECT headline FROM headlines WHERE category_id IN (1, 2, 3, ...);

If you filter your headlines in PHP, think how many you'll be throwing away. If you end up with removing just 10% of the headlines, it won't matter as much as when you'd be throwing away 90% of the results.

andri
The most extreme example of this I've worked with was re-writing a multiple query with PHP processing block of code (written by a beginning coder) to use a single query that returned the desired results. Processing time reduced from over 20 seconds to nearly instantaneous.
Dennis Palmer
Is each item provided by using IN an array of values?
Tim
A: 

MySQL can already do the selecting and ordering of the data for you. I suggest to be lazy and use this.

Also I'd look for a (1) query that fetches all the categories and their headlines at once. Would an ORDER BY category, publishdate or something do?

Onots
A: 

Every trip to the database costs you something. Returning extra data that you then decide to ignore costs you something. So you're almost certainly better to let the database do your pruning.

I'm sure one could come up with some case where deciding what data you need makes the query hugely complex and thus difficult for the database to optimize, while you could do it in your code easily. But if we're talking about "select headline from story where category='Sports'" followed by "select headline from story where category='Politics'" then "select headline from story where category='Health'" etc, versus "select category, headline from story where category in ('Health','Sports','Politics')", the latter is clearly better.

A: 

These kinds of questions are always hard to answer because the situation determines the best course. There is never a truly correct answer, only better ways. In my experience doesn't really matter whether you attempt to do the work in PHP or in the database because you should always try to cache the results of any expensive operation using a caching engine such as memcached. That way you are not going to spend a lot of time in the db or in php itself since the results will be cached and ready instantaneously for use. When it comes down to it, unlss you profile your application using a tool like xDebug, what you think are your performance bottlenecks are just guesses.

Matthew Purdon