CODEWITHSUNDEEP
phpmysql
user1053700
user1053700

Reputation: 73

Joining a count query mysql for performance

Have searched but can't find an answer which suits the exact needs for this mysql query.

I have the following quires on multiple tables to generate "stats" for an application:

SELECT COUNT(id) as count FROM `mod_**` WHERE `published`='1';

SELECT COUNT(id) as count FROM `mod_***` WHERE `published`='1';

SELECT COUNT(id) as count FROM `mod_****`;

SELECT COUNT(id) as count FROM `mod_*****`;

pretty simple just counts the rows sometimes based on a status.

however in the pursuit of performance i would love to get this into 1 query to save resources.

I'm using php to fetch this data with simple mysql_fetch_assoc and retrieving $res[count] if it makes a difference (pro isn't guaranteed, so plain old mysql here).

Upvotes: 2

Views: 64

Answers (4)

ericpap
ericpap

Reputation: 2937

As @Halcyon said there is not much to gain here. You can anyway do several UNIONS to get all the result in one query

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65294

Disagreeing with @Halcyon I think there is an appreciable difference, especially if the MySQL server is on a different machine, as every single query uses at least one network packet.

I recommend you UNION the queries with a marker field to protect against the unexpected.

Upvotes: 0

Marc B
Marc B

Reputation: 360732

You can use something like

SELECT SUM(published=1)

for some of that. MySQL will take the boolean result of published=1 and translate it to an integer 0 or 1, which can be summed up.

But it looks like you're dealing with MULTIPLE tables (if that's what the **, *** etc... are), in which case you can't really. You could use a UNION query, e.g.:

SELECT ...
UNION ALL
SELECT ...
UNION ALL 
SELECT ...
etc...

That can be fired off as one single query to the DB, but it'll still execute each sub-query as its own query, and simply aggregate the individual result sets into one larger set.

Upvotes: 1

Halcyon
Halcyon

Reputation: 57728

The overhead of sending a query and getting a single-row response is very small.

There is nothing to gain here by combining the queries.

If you don't have indexes yet an INDEX on the published column will greatly speed up the first two queries.

Upvotes: 5

Related Questions