routes mailcoach.emailLists and mailcoach.emailLists.subscribers slow with 800k subscribers

[electronick86]

Hello!

One of the project I'm working on with mailcoach (currently v2.23.13) has 800k subscribers. Once the subscribers are imported, the routes mailcoach.emailLists and mailcoach.emailLists.subscribers becomes very slow.

I saw (thanks telescope) thatone query tooks ~10seconds...(on my macbook pro 16 with valet and also on a 8vcpu server) I think one new index could be created. Can you give me your point of view?

 SELECT
    `mailcoach_email_lists`.*,
    (
        SELECT
            count(id)
        FROM
            `mailcoach_subscribers`
        WHERE
            `subscribed_at` IS NOT NULL
            AND `unsubscribed_at` IS NULL
            AND `mailcoach_subscribers`.`email_list_id` = `mailcoach_email_lists`.`id`) AS `active_subscribers_count`
    FROM
        `mailcoach_email_lists`
    ORDER BY
        `name` ASC
    LIMIT 15 OFFSET 0;

Execution time : 11.544 s

CREATE INDEX `test` ON `immovlan_mail`.`mailcoach_subscribers` (`email_list_id`,`subscribed_at`,`unsubscribed_at`) USING BTREE;

With this index created, same request take 385 ms .

Do you see any downside to create this extra index. There is no so many insert/updates to this table. Or a better way to solve this problem?

Thanks!

[connecteev]

I haven't encountered this yet, but wow....11 seconds to 385ms, that's amazing!

[freekmurze]

@electronick86 that's quite amazing. Could you send a PR to the v3 branch that adds this index to the default migration?

[riasvdv]

Added in the v3 branch