CODEWITHSUNDEEP
mysqlsqlquery-optimization
kelvin noordenbos
kelvin noordenbos

Reputation: 11

My query is really slow even though it has an index

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.

Upvotes: 0

Views: 143

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 1

Related Questions