2

Let's assume I have the following query:

SELECT address
FROM addresses a, names n
WHERE a.address_id = n.address_id
GROUP BY n.address_id
HAVING COUNT(*) >= 10

If the two tables were large enough (think if we had the whole US population in these two tables) then running an EXPLAIN on this SELECT would say that Using temporary; Using filesort which is usually not good.

If we have a DB with many concurrent INSERTs and SELECTs (like this) would delegating the GROUP BY a.address_id HAVING COUNT(*) >= 10 part to PHP be a good plan to minimise DB resources? What would the most efficient way (in terms of computing power) to code this?

EDIT: It seems the consensus is that offloading to PHP is the wrong move. How then, could I improve the query (let's assume indexes have been created properly)? More sepcifically how do I avoid the DB from creating a temporary table?

Improve this question
1
  • Depending on how volatile your data is you might be able to reduce the bottleneck by utilising database caching (if your db server supports it); you could also file-cache the results from the query and load them into PHP over the file system. It's less optimal but if the bottleneck is at the db server it might help as a workaround. Commented Oct 25, 2011 at 17:15

2 Answers 2

Reset to default
5

So your plan to minimize resources is by sucking all the data out of the database and having PHP process it, causing extreme memory usage?

Don't do client-side processing if at all possible - databases are DESIGNED for this sort of heavy work.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

yes the idea would be exactly this. the db is the bottlenck in my server and i was hoping to unload some of the work
set up replica databases to do the processing there. e.g... throw more hardware at the problem. Moving your processing from MySQL to PHP is just going to change move the location of the problem. PHP is NOT efficient for memory usage.
@AdrienHingert If you are going to do sorting and grouping and whatever you need to first transfer ALL of the data to the memory so that PHP can use it. When you do this you can't take advantage of the indexes that the DB has created etc. Databases are optimized for this kind of work. If there's a possibility for you to cache the data it might speed things up for you.
2

Offloading this to PHP is probably the opposite direction you want to go. If you must do this on a single machine then the database is likely the most efficient place to do it. If you have a bunch of PHP machines and only a single DB server, then offloading might make sense, but more likely you'll just clobber the IO capability of the DB. You'll probably get a bigger win by setting up a replica and doing your read queries there. Depending on your ratio of SELECT to INSERT queries, you might want to consider keeping a tally table (many more SELECTs than INSERTs). The more latency you can allow for your results, the more options you have. If you can allow 5 minutes latency, then you might start considering a distributed batch processing system like hadoop rather than a database.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.