I have a query that gets all user ids that has logged in within the past year.
Query1:
SELECT user_id, user_name
FROM logins
WHERE login_date BETWEEN "2015-09-01" AND "2016-09-01"
GROUP BY user_id;
Execution time: 625ms
And another query similar to above, now with additional filters to list the usernames with 'jeff'.
Query2:
SELECT user_id, user_name
FROM logins
WHERE login_date BETWEEN "2015-09-01" AND "2016-09-01"
AND user_name LIKE '%jeff%'
GROUP BY user_id;
Execution time: 587ms
Total execution time is 1212ms.
I use Query1 only to get the total number of logged in users within the year and Query2 to get the list of users with user_name like 'jeff'.
The problem is, I need to execute both queries and it takes longer.
Is there a way to combine both queries into just one and get the data I need i.e. the total count of logged in users and the list of logged in users with user_name like 'jeff'? at the same time minimizing the execution time?
Sample data:
user_id user_name login_date
1 annie 2015-10-02
2 greg 2015-12-25
3 jeff 2015-11-05
4 aljeffar 2016-03-06
5 jeff1 2015-07-07
2 greg 2016-01-30
1 annie 2015-11-13
3 jeff 2016-02-14
2 greg 2016-09-30
3 jeff 2016-07-04
Query1 Result:
user_id user_name 1 annie 2 greg 3 jeff 4 aljeffar Total result count: 4 -- this should be the fix value of total_loggedin_users column below
Query2 Result:
user_id user_name 3 jeff 4 aljeffar
Expected Final Result:
user_id user_name total_loggedin_users 3 jeff 4 4 aljeffar 4
SELECT
user_id,
count(*) as total,
sum(case when user_name LIKE '%jeff%' then 1 else 0 end) as jeff_count
FROM logins
WHERE login_date BETWEEN "2015-09-01" AND "2016-09-01"
GROUP BY user_id;
Run it just once and add a conditional sum to count jeffs.