My query is really slow even though it has an index

Question

We have to check 7 million rows to make campagne statistics. It takes around 30 seconds to run the query and it doesnt improve with indexes.

Indexes didnt change the speed at all.

I tried adding indexes on the where fields, the where fields + group by and the where fields + sum.

Server type is MYSQL and the server version is 5.5.31.

SELECT
    NOW(), `banner_campagne`.name, `banner_view`.banner_uid, SUM(`banner_view`.fetched) AS fetched,
    SUM(`banner_view`.loaded) AS loaded,
    SUM(`banner_view`.seen) AS seen
FROM `banner_view` INNER JOIN
     `banner_campagne`
     ON `banner_campagne`.uid = `banner_view`.banner_uid AND
        `banner_campagne`.deleted = 0 AND 
        `banner_campagne`.weergeven = 1
WHERE
    `banner_view`.campagne_uid = 6 AND `banner_view`.datetime >= '2019-07-31 00:00:00' AND `banner_view`.datetime < '2019-08-30 00:00:00'
GROUP BY
    `banner_view`.banner_uid

I expect the query to run around 5 seconds.

Gordon Linoff · Accepted Answer · 2019-08-29 12:25:06Z

1

The indexes that you want for this query are probably:

banner_view(campagne_uid, datetime)
banner_campagne(banner_uid, weergeven, deleted)

Note that the order of the columns in the index does matter.

answered Aug 29, 2019 at 12:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

My query is really slow even though it has an index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related