986016 - SUMO query optimization: Date extracts from the wiki_helpfulvote table

Status: RESOLVED WONTFIX

(support.mozilla.org :: Code Quality, task)

Description

[Sheeri Cabral [:sheeri]]

#2.)  SELECT (extract( year FROM created )) AS `year`, (extract( day FROM created )) AS `day`, (extract( month FROM created )) AS `month`, COUNT(`wiki_helpfulvote`.`created`) AS `count` FROM `wiki_helpfulvote` WHERE (`wiki_helpfulvote`.`created` >= %s  AND `wiki_helpfulvote`.`helpful` = %s ) GROUP BY (extract( year FROM created )), (extract( day FROM created )), (extract( month FROM created )) ORDER BY NULL;

     -- This query extracts every single thing it can out of a datetime column. It took 30-45 seconds to complete per execution because of the lack of index usage. There are better options here: My suggestion would be to implement one of the below (B is the better option): 
         A.) parse out the day/month/year in the app after retreiving just the date, and store the year as an extra column to satisfy the group by.
         B.) actually store the day/month/year in the database separately so that indexes can be used.

     -- This query is a full table scan, and can be resolved by adding an additional index after implementing either of the solutions above.

     -- Based upon the other queries going across this table, I believe option B is the best option.

Comment 1

[Sheeri Cabral [:sheeri]]

Removing the Data team as cc's, as this is a recommendation, and can be used or not used as seen fit. If you need more information, please re-add myself and Matt Pressman. Also flipping to the SUMO product.

Comment 2

[Seburo]

Closed as per https://blog.mozilla.org/sumo/2019/06/07/sumo-platform-roadmap/