I have mysql 5.0.22

GROUP BY and HAVING don't really "help" the database server, right? I
mean, it basically has to do more work than if I just read in all the
results? It's a heavier query if I do group by and having, right?

Instead of group by and having, I'm willing to sort out the results in
my code instead, if it's easier for the db.

Similarly, is limit x, offset x helping the db at all, or is it better
for the db server if I do it in my code (on the webserver) instead?

thanks

On Jan 10, 8:55*pm, dterr... (AT) hotmail (DOT) com wrote:
First define HEAVY.

So you think transporting say 10million rows to your client machine
and sorting them there is easier?


What are you drinking? 8^)

I have seldom used LIMIT, preferring instead to open the cursor, fetch
what I need and close the cursor. (That is not always possible in web
apps unfortumately.)

Think about what are the implications of what you are doing. Network
traffic is a cost too. Software Engineering is about knowing the
issues and making the tradeoffs to benefit of our customer/boss.
Ed

On Jan 10, 8:55*pm, dterr... (AT) hotmail (DOT) com wrote:
It's very hard to imagine a scenario where it would be better to sort
at the client. This sounds like micro-optimisation (the end result of
which is often pessimisation).

<dterrors (AT) hotmail (DOT) com> wrote

Read what Ed and Toby said. Generating more network traffic, and addding
more load to the machine where your code is executed may well make things
worse than performing GROUP BY and HAVING on the server. Test your theories
about how much load you are putting on your server. GROUP BY and HAVING
were added to SQL precisely so that this kind of work could be loaded onto
database servers. The people who did this were not idiots, and they may
have understood some things you have yet to understand.

The biggest problem people who take your approach face is that it takes
their minds off of the purpose of the application they are building and onto
writing algorithms for the efficient reduction of data. These algorithms
are likely to be the same as, or even not as good as, the algorithms coded
into the DBMS by the engineers who built it.

If you want to optimize, here are some things to consider optimizing:

The logical model of your data. A simple and sound logical model that meets
the requirements and is simple to use without being too simple is a powerful
optimization of your whole system. Your queries will be lots easier to
write and manage, and they will run pretty fast as well. Optimize this for
simplicity.

The physical model of your database design. The physical data model should
be guided by the logical model. In addition, judicious use of the features
that are offered by your DBMS can make your server run ten times faster,
without locking your application in to that DBMS. If you were to move to a
different DBMS you would have to repeat this exercsie, but you wouldn't
necessarily have to rewrite lots of application code. Optimize this for
performance.

Your application itself. Optimize this for performance, but also for
revisability. The more successful your application is, the more rework it's
going to end up needing, either by you or your successor. If it's all tied
up in Gordian knot, it's going to be impossible to work with.