Take the 2-minute tour ×

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.

share|improve this question
What is the source of your category id's? Do you have a categories table? Are you getting ALL categories and headlines, or only selected categories? –  CoderDennis May 18 '09 at 16:55
I hadn't decided yet whether I was just going to have a category field per each headline row or to split it off as it's own table. –  Tim May 18 '09 at 17:16
add comment

5 Answers

up vote 8 down vote accepted

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.

share|improve this answer
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. –  CoderDennis May 18 '09 at 16:53
Is each item provided by using IN an array of values? –  Tim May 18 '09 at 16:53
add comment

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.

share|improve this answer
add comment

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?"

share|improve this answer
add comment

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?

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


