CODEWITHSUNDEEP
mysqlperformancesql-order-by
Ivan Velichko
Ivan Velichko

Reputation: 6709

MySql query with conditional group by performance

I have pretty simple table with about 1 000 000 rows and I want to retrieve some aggregate statistics from its. It is not analytic request, it is user data. I can retrieve the data with 3 separate queries each of which will be with specific where clause. But it will be THREE query to my DB server. And also I can do ONE query with conditional ORDER BY clause. But I never used such queries earlier on production. Should I expect some troubles with it?

CREATE TABLE t1 (id INTEGER, fk_1 INTEGER, field_1 INTEGER, field_2 DATETIME);
 ... INSERT INTO t1 ... # 1 000 000 times

SELECT COUNT(*) FROM t1 
WHERE fk_1 = <some_value> 
GROUP BY CASE WHEN (field_1 = 1) THEN 1 ELSE (CASE WHEN field_2 > NOW() THEN 0 ELSE -1 END) END;

Upvotes: 0

Views: 2041

Answers (1)

Mark
Mark

Reputation: 8441

try to use the ID in count like:

SELECT COUNT(ID) FROM t1 
WHERE fk_1 = <some_value> 
GROUP BY CASE WHEN (field_1 = 1) THEN 1 ELSE (CASE WHEN field_2 > NOW() THEN 0 ELSE -1 END) END;

Upvotes: 5

Related Questions