How do I identify non-linear increase in time for MySQL query?

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           |

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

I still don’t know why this was running slow but I have found a solution that increased the speed and will show the steps on how I got there.

Firstly, in test 2 two and test 3 in the original question, you can see that the individual query to the logins table was pretty quick, but when querying multiple channel_partner_id the entire query became very slow. I suspected that it was to do with how I was checking the channel_partner_id against the logins table.

I rewrote the query to get the channel_partner_id‘s from a list instead of from a select. The end result looks like this:

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
    )
)

This query took 6.93 seconds which is still pretty slow but it’s much closer to the time that I expected the query would take.

I can’t explain why one way is faster and one way is slower.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x