MySQL - Multiple Queries or Single Query

Question

So I have a table that contains votes. The relevant columns for this issue being user and timestamp.

I need to grab the user's total vote count, and also their votes this month.

I know the queries - I'm not asking for those. I use these at the same time:

Votes this month / Total votes:

SELECT COUNT( 0 ) FROM votes WHERE ( timestamp BETWEEN DATE_FORMAT( NOW( ) ,'%Y-%m-01' ) AND NOW( ) ) AND user = ?;

SELECT COUNT( 0 ) FROM votes WHERE user = ?;

At the moment, my database isn't large enough (or even queried enough) to where performance is an issue. However, that's expected to change shortly. Should I keep the queries separate, or should I:

SELECT COUNT( 0 ) AS totalVotes,
       SUM( IF( timestamp BETWEEN DATE_FORMAT( NOW( ) ,'%Y-%m-01' )
                              AND NOW( ), 1, 0 ) ) AS votesThisMonth
    FROM votes WHERE user = ?;

What is the best practice? Are there any tips for querying multiple bits of information from the same table to prevent having to search it twice? Is my combined query even what I should be using?

Thanks!

Why would you use count(0) instead of count(*) (the standard) or count(1) (which to me makes semantic sense)? — Gordon Linoff
– Gordon Linoff, Commented May 30, 2020 at 12:49

Rick James · Accepted Answer · 2020-05-31 04:43:34Z

2

I would recommend the second solution, that uses a unique query with a conditional sum.

Rationale: to produce the second resultset, you need to scan a subset of the first resultset. So the additional processing needed is quite tiny anyway. On the other hand, running two separate queries involves one more roundtrip to the server, one more query parse for the query planner, and an additional scan on the table.

For performance, you want an index on (user, timestamp).

edited May 31, 2020 at 4:43

Rick James

144k15 gold badges144 silver badges255 bronze badges

answered May 30, 2020 at 3:12

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2020-05-30 12:51:23Z

1

In MySQL, I would recommend:

SELECT COUNT(*) AS totalVotes,
       SUM(EXTRACT(YEAR_MONTH FROM timestamp) = EXTRACT(YEAR_MONTH FROM NOW())) AS votesThisMonth
FROM votes
WHERE user = ?;

An alternative to the above is:

 SUM(timestamp >= CURRENT_DATE - (DAY(CURRENT_DATE) - 1) DAY)

I strongly discourage you from using strings for dates, unless you really have to. There are multiple ways to get values from the current date that do NOT involves implicitly or explicitly converting a date/time value to a string.

Also, the IF() is redundant. MySQL allows you to just add up boolean values. Neither is standard SQL, so you might as well use the more concise version.

The COUNT(0) is jarring to me. Although it works, COUNT(*) or COUNT(1) seem simpler.

answered May 30, 2020 at 12:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Stev Over a year ago

Thank you, Gordon - your tips were very helpful. Your votesThisMonth is much easier to read and understand. Also, I didn't know about MySQL adding booleans.

Gordon Linoff Over a year ago

@Stev . . . You are free to accept whatever answer you think is best. Based on your comments, I'm surprised you didn't accept this one.

Rick James · Accepted Answer · 2020-05-30 21:43:13Z

1

Slightly different, not necessarily faster than other suggestions:

SELECT COUNT(*) AS total,
       SUM(LEFT(timestamp, 7) = LEFT(NOW(), 7)) AS this_month
    FROM tbl
    WHERE user_id = ?

And have

INDEX(user_id, timestamp) -- in this order.  ("Covering")

Back to the question of 1 vs 2 queries:

My combined query makes one scan over all rows for the user.
The 'total' query also needs one scan over all rows for the user.
The 'this month' query, if using BETWEEN or >= makes a scan over just the rows for the user this month. (Mine fails to do that, but it does not matter.)

answered May 30, 2020 at 21:43

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

MySQL - Multiple Queries or Single Query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related