[Resolved] one big&bad or multiple&tiny queries?

bad76

Hi all!!!
I've a doubt with mysql...

I've a very big query, with a GROUP BY, some SUM() and COUNT(), and an OR.
But at the end I have just LIMIT 1,18

Now, I can use one more tiny query to get the 18 elements, and then for each one I do one query with GROUP BY, SUM, COUNT() and OR.

Well, this will speeds up my work ?!?

It's better a one single query and let the optimization to the mysql parser, or 19 distinct queries ?

Thanks for any answer...

mtimdog

the answer is...i don't know. Depending on the complexity it could go either way (I'm leaning toward big and bad). Time it to see though.

AstroTeg

Sometimes you just have to experiment. It depends on the database engine and how it optimizes the queries.

It typically is much easier for a database engine to group a small data set then to group a large data. But making multiple queries to a database can be expensive (in terms of time).

Sxooter

Well, the aggregation has to be done either once on a medium to large set, or several times on smaller sets.

Assuming the single large set can fit easily into memory and doesn't need to spill to disk for sorting, then the one single large query should be faster.

On the other hand, if the one large aggregation is way big, then the many small queries may win.

But my money's on the single large aggregate being faster.

I use PostgreSQL, which has much improved its aggregation speed of late. I'm not familiar with MySQL aggregation performance that much, so if there's an inefficiency in handling large data sets being aggregated, the many small could win.

I'd definitely benchmark the two, both with the data in cache, and with enough time / activity between marks to allow the test to be done with empty caches.

bad76

Ok.
I will buy a good chronometer and will do some test with both solution.

Thanks all.

Sxooter

Oh, and make sure you've tuned your database to use more cache memory for things like this.

If you have the database set to use only a small cache / buffer area, it may well be faster to do the many aggs. then, upping the buffer will likely make the single big agg faster.

Just a guess though.