CODEWITHSUNDEEP
mysqloptimizationcountsum
Aley
Aley

Reputation: 8640

How to optimize a MySQL query with SUM(condition)

I have the following MySQL query.

  SELECT user_id
         SUM(reached = 'Y') AS reached_count,
         SUM(reached = 'N') AS not_reached_count
    FROM goals
GROUP BY user_id

In table goals I have around 2 million entries. The query takes around 45 seconds to execute.

The heavy part seems to be SUM(reached = 'Y') which is taking quite long. I compared it with COUNT(*) which was indeed much faster, but was not distinguishing between Y and N.

EDIT: reached is of type ENUM('Y','N')

Upvotes: 0

Views: 99

Answers (2)

user359040
user359040

Reputation:

Try adding an index on user_id, reached, then try the query:

SELECT user_id, reached, count(*)
FROM goals
GROUP BY user_id, reached

Upvotes: 2

Stephan
Stephan

Reputation: 8090

The heavy part is the GROUP BY user_id especially if you have a lot of users so i suggest to add ORDER BY NULL which will help a little , you can read more here

Upvotes: 0

Related Questions