CODEWITHSUNDEEP
mysql
adoweb
adoweb

Reputation: 47

Optimise MySQL query with subqueries

What is the best way to optimize this query? Is it possible to generate an index? Without optimization it takes 8s.

SELECT
    partner_banner_views AS views,
    (
      SELECT
        count(statistics_partner_id)
      FROM
        statistics_partner
      WHERE
        statistics_partner_partner_id = partner_id
      AND statistics_partner_click_date IS NOT NULL
    ) AS clicks,
    round (
    (
        100 *(
            SELECT
                count(statistics_partner_id)
            FROM
                statistics_partner
            WHERE
                statistics_partner_partner_id = partner_id
            AND statistics_partner_click_date IS NOT NULL
        )/ partner_banner_views
    ),
    3
) AS ctr
FROM partners

THX

Upvotes: 1

Views: 44

Answers (1)

Vatev
Vatev

Reputation: 7590

You will need an index on statistics_partner_partner_id or statistics_partner_partner_id,statistics_partner_click_date if you expect to have a lot of NULL click dates.

You can convert it to a JOIN like this:

SELECT
    partner_banner_views AS views,
    COUNT(statistics_partner_partner_id) as clicks,
    ROUND(100 * clicks / COUNT(statistics_partner_partner_id), 3) as ctr
FROM partners
LEFT JOIN statistics_partner ON statistics_partner_partner_id = partner_id 
                                AND statistics_partner_click_date IS NOT NULL
GROUP BY partner_id

Upvotes: 1

Related Questions