Below are two queries that I am running. One takes between 75-80 seconds and one that takes 1.0-1.5 seconds. Both of these results show the expected 50 rows of channel_administrators.channel_partner_ids. The difference between the faster and slower query is the SELECT selecting unique logins from the login table. The login table has 460833 rows and I understand that this should slow down the query. The reason I find this unexpected is that when running this code individually on one channel_administrators.channel_partner_id the results come back in about 0.2 to 0.7 seconds for the largest channel_administrators.channel_partner_id and for 50 results I wouldn't expect it to take more than 50-seconds.
I would expect that the time increase would be linear at the very worst for the time increase but the time increase seems to be more than that. This non-linear increase makes me feel like I'm doing something (very?) wrong but I don't know how to find out what is wrong with my query. Can anyone tell me why there is a non-linear time increase in this query?
I've included some testing queries that I've run and their latest timings at the bottom of the post.
Edit: I think the best example of this phenomenon is looking at Test 2 and Test 3. These examples are as stripped back as they can get and it shows the running the logic once goes quickly but 50 times goes very very slowly.
EDIT 2: I've added more data getting the same results in 6.93 seconds rather than 75+ seconds. For my system I think this is an acceptable result. I will write up an answer to this question now.
80-second query:
SELECT
info.managed_id,
info.channel_name,
info.registered_users,
info.new_users,
info.active_users,
info.coupon_opens
FROM channel_administrators
LEFT JOIN (
SELECT
channel_partners.id AS managed_id,
channel_partners.name as channel_name,
(
SELECT COUNT(users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
) AS registered_users,
(
SELECT COUNT(DISTINCT users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
AND users.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS new_users,
(
SELECT COUNT(DISTINCT logins.user_id)
FROM logins
WHERE logins.channel_partner_id = channel_partners.id
AND logins.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS active_users,
(
SELECT COUNT(coupon_trackings.id) AS coupon_view_count
FROM coupon_trackings
WHERE coupon_trackings.channel_partner_id = channel_partners.id
AND coupon_trackings.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS coupon_opens
FROM channel_partners
) AS info
ON managed_id = channel_administrators.channel_partner_id
WHERE channel_administrators.user_id = 54184
ORDER BY info.channel_name
1.5-second query (Commented out difference):
SELECT
info.managed_id,
info.channel_name,
info.registered_users,
info.new_users,
-- info.active_users,
info.coupon_opens
FROM channel_administrators
LEFT JOIN (
SELECT
channel_partners.id AS managed_id,
channel_partners.name as channel_name,
(
SELECT COUNT(users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
) AS registered_users,
(
SELECT COUNT(DISTINCT users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
AND users.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS new_users,
-- (
-- SELECT COUNT(DISTINCT logins.user_id)
-- FROM logins
-- WHERE logins.channel_partner_id = channel_partners.id
-- AND logins.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
-- ) AS active_users,
(
SELECT COUNT(coupon_trackings.id) AS coupon_view_count
FROM coupon_trackings
WHERE coupon_trackings.channel_partner_id = channel_partners.id
AND coupon_trackings.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS coupon_opens
FROM channel_partners
) AS info
ON managed_id = channel_administrators.channel_partner_id
WHERE channel_administrators.user_id = 54184
ORDER BY info.channel_name
Below are queries I've used to test individual times on the channels with the largest results.
Test 1: 0.441s - For individual biggest channel:
SELECT
channel_partners.id AS managed_id,
channel_partners.name as channel_name,
(
SELECT COUNT(users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
) AS registered_users,
(
SELECT COUNT(DISTINCT users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
AND users.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS new_users,
(
SELECT COUNT(DISTINCT logins.user_id)
FROM logins
WHERE logins.channel_partner_id = channel_partners.id
AND logins.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS active_users,
(
SELECT COUNT(coupon_trackings.id) AS coupon_view_count
FROM coupon_trackings
WHERE coupon_trackings.channel_partner_id = channel_partners.id
AND coupon_trackings.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS coupon_opens
FROM channel_partners
WHERE channel_partners.id = 3255770
Test 2: 0.368s - Active users for the biggest channel:
SELECT COUNT(DISTINCT logins.user_id)
FROM logins
WHERE logins.channel_partner_id = 3255770
AND logins.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
Test 3: 75.2s Just login information
SELECT
info.managed_id,
info.channel_name,
info.active_users
FROM channel_administrators
LEFT JOIN (
SELECT
channel_partners.id AS managed_id,
channel_partners.name as channel_name,
(
SELECT COUNT(DISTINCT logins.user_id)
FROM logins
WHERE logins.channel_partner_id = channel_partners.id
AND logins.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS active_users
FROM channel_partners
) AS info
ON info.managed_id = channel_administrators.channel_partner_id
WHERE channel_administrators.user_id = 54184
Test 4: 6.93s - Making Progress with rewrite
SELECT
channel_partners.id AS managed_id,
channel_partners.name as channel_name,
(
SELECT COUNT(users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
) AS registered_users,
(
SELECT COUNT(DISTINCT users.id)
FROM users
WHERE users.channel_partner_id = channel_partners.id
AND users.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS new_users,
(
SELECT COUNT(DISTINCT logins.user_id)
FROM logins
WHERE logins.channel_partner_id = channel_partners.id
AND logins.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS active_users,
(
SELECT COUNT(coupon_trackings.id) AS coupon_view_count
FROM coupon_trackings
WHERE coupon_trackings.channel_partner_id = channel_partners.id
AND coupon_trackings.created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
) AS coupon_opens
FROM channel_partners
WHERE (channel_partners.id IN (SELECT
channel_administrators.channel_partner_id
FROM channel_administrators
WHERE channel_administrators.user_id = 54184
)
)
EDIT: Adding results from queries (removed names)
80s query:
|managed_id|registered_users|new_users|active_users|coupon_opens|
|----------|----------------|---------|------------|------------|
|14 |1146 |46 |282 |893 |
|27 |2159 |48 |206 |635 |
|15 |2039 |68 |490 |2560 |
|16 |15 |0 |1 |0 |
|20 |1391 |53 |413 |1614 |
|21 |3 |0 |0 |0 |
|43 |1051 |36 |255 |1234 |
|44 |706 |19 |85 |276 |
|46 |16 |0 |4 |8 |
|47 |68 |1 |5 |30 |
|48 |169 |6 |40 |308 |
|49 |408 |13 |118 |434 |
|52 |52 |1 |11 |54 |
|53 |378 |11 |111 |391 |
|54 |34 |1 |5 |57 |
|75 |576 |7 |59 |145 |
|3255347 |773 |12 |99 |167 |
|685131 |142 |0 |9 |91 |
|76 |22 |0 |9 |25 |
|55 |276 |5 |68 |251 |
|56 |2232 |79 |534 |1644 |
|57 |78 |0 |10 |47 |
|58 |708 |10 |109 |364 |
|59 |1274 |42 |465 |1929 |
|60 |133 |0 |37 |97 |
|3 |0 |0 |127 |257 |
|2144749 |0 |0 |4 |40 |
|61 |629 |9 |119 |363 |
|63 |857 |36 |267 |892 |
|64 |49 |1 |13 |21 |
|65 |723 |15 |281 |1152 |
|66 |77 |0 |17 |48 |
|67 |123 |10 |59 |190 |
|68 |693 |8 |191 |387 |
|70 |80 |0 |31 |58 |
|71 |214 |1 |41 |102 |
|72 |104 |2 |23 |49 |
|3255770 |3149 |86 |542 |2280 |
|3255771 |3012 |39 |526 |2056 |
|77 |180 |9 |89 |239 |
|477 |677 |5 |286 |583 |
|478 |335 |191 |235 |2226 |
|479 |162 |12 |51 |159 |
|480 |57 |0 |8 |12 |
|302 |51 |3 |17 |32 |
|303 |213 |37 |116 |598 |
|373109 |9 |3 |6 |4 |
|373110 |10 |2 |5 |0 |
|373111 |29 |9 |16 |29 |
|3255810 |0 |0 |0 |0 |
2s query:
|managed_id|registered_users|new_users|coupon_opens|
|----------|----------------|---------|------------|
|14 |1146 |46 |893 |
|27 |2159 |48 |635 |
|15 |2039 |68 |2560 |
|16 |15 |0 |0 |
|20 |1391 |53 |1614 |
|21 |3 |0 |0 |
|43 |1051 |36 |1234 |
|44 |706 |19 |276 |
|46 |16 |0 |8 |
|47 |68 |1 |30 |
|48 |169 |6 |308 |
|49 |408 |13 |434 |
|52 |52 |1 |54 |
|53 |378 |11 |391 |
|54 |34 |1 |57 |
|75 |576 |7 |145 |
|3255347 |773 |12 |167 |
|685131 |142 |0 |91 |
|76 |22 |0 |25 |
|55 |276 |5 |251 |
|56 |2232 |79 |1644 |
|57 |78 |0 |47 |
|58 |708 |10 |364 |
|59 |1274 |42 |1929 |
|60 |133 |0 |97 |
|3 |0 |0 |257 |
|2144749 |0 |0 |40 |
|61 |629 |9 |363 |
|63 |857 |36 |892 |
|64 |49 |1 |21 |
|65 |723 |15 |1152 |
|66 |77 |0 |48 |
|67 |123 |10 |190 |
|68 |693 |8 |387 |
|70 |80 |0 |58 |
|71 |214 |1 |102 |
|72 |104 |2 |49 |
|3255770 |3149 |86 |2280 |
|3255771 |3012 |39 |2056 |
|77 |180 |9 |239 |
|477 |677 |5 |583 |
|478 |335 |191 |2226 |
|479 |162 |12 |159 |
|480 |57 |0 |12 |
|302 |51 |3 |32 |
|303 |213 |37 |598 |
|373109 |9 |3 |4 |
|373110 |10 |2 |0 |
|373111 |29 |9 |29 |
|3255810 |0 |0 |0 |
Each table needs this composite index with the columns in the given order:
INDEX(channel_partner_id, created_at)
And DROP the corresponding index on just channel_partner_id if you have such.
Add this "covering" index to channel_administrators
INDEX(user_id, channel_partner_id)
and get rid of INDEX(user_id) if it exists.
Simplify the query by not having nested Selects:
SELECT cp.id AS managed_id,
cp.name as channel_name,
( SELECT COUNT(users.id)
FROM users
WHERE users.channel_partner_id = cp.id
) AS registered_users,
((etc))
FROM channel_partners AS cp
JOIN channel_administrators AS ca
ON cp.managed_id = ca.channel_partner_id
WHERE ca.user_id = 54184
ORDER BY channel_name
Tip: Consider changing
created_at BETWEEN '2021-06-03' AND '2021-07-03' -- 30 days
to
created_at >= '2021-06-03'
AND created_at < '2021-06-03' + INTERVAL 30 DAY
or use + INTERVAL 1 MONTH if more appropriate.
If you still have performance issues, let's see what the query looks like and provide SHOW CREATE TABLE.