Have Geek Brain

Faster query execution in MYSQL or use PHP logic

I have a page that will pull many titles from multiple categories based on the category ID.

I am wondering if it makes sense to pull all the headers and then sort them through PHP if / ifelse, or is it better to run multiple requests, each containing headers from each category.

+1


a source to share


5 answers


Why not do it in one request? Sort of:

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


If you are filtering headers in PHP, consider how much you will throw away. If you end up removing only 10% of the titles, it won't matter as much as if you select 90% of the results.

+8


a source


Such questions are always difficult to answer because the situation determines the best course. There is never a right answer, only better ways. In my experience, it doesn't matter if you're trying to do the job in PHP or on a database, because you should always try to cache the results of any expensive operation using a caching mechanism like memcached . This way you are not going to spend a lot of time on db or php, as the results will be cached and ready instantly for use. When it comes down to it, you can profile your application with a tool like xDebug , which you think is your performance bottleneck, it's just guesswork.



+2


a source


It is usually best not to overload the DB because you can cause a bottleneck if you have a lot of concurrent requests.

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

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

0


a source


MySQL can already do the selection and ordering for you. I suggest being lazy and using that.

Also I would look for (1) a query that fetches all categories and titles at once. Will the category ORDER BY, publish or do something?

0


a source


Each trip to the database costs you something. Returning additional data that you then choose to ignore costs you something. This way you will almost certainly let the database do the pruning.

I'm sure you can think of some case where deciding what data you need makes the query extremely complex and therefore difficult to optimize the database while you can easily do it in your code. But if we're talking about "select a title from history where category =" Sports "and then" select a title from history where category = "Politics" then "select a title from history where category =" Health "etc ., Unlike "select a category, heading from history, where the category (" Health "," Sports "," Politics "), the latter is clearly better.

0


a source






More articles:


All Articles