![]() SUM(CASE WHEN month(dates)='8' and year(dates)='2018' THEN all_impressions END) AS `imp (Aug-2018)`, | version_comment | Source distribution |Īs per comments from Akina, I've got it to around 6 seconds (after caching) with the following: select I'm hoping there is another query or table optimisation I can do - alternatively I could pre-aggregate the data but I'd rather avoid that. The current table size is closing in on 15 million rows. This query is used to load a table to display data to users so needs to be loading within 3 seconds, ideally. The table data is fairly easy just a URL for the landing_page column and the rest are int or floats (excluding the dates column of course). | desktop_positions | float | YES | | NULL | | | desktop_clicks | int(11) | YES | | NULL | | | desktop_impressions | int(11) | YES | | NULL | | | tablet_positions | float | YES | | NULL | | | tablet_clicks | int(11) | YES | | NULL | | | tablet_impressions | int(11) | YES | | NULL | | | mobile_positions | float | YES | | NULL | | | mobile_clicks | int(11) | YES | | NULL | | | mobile_impressions | int(11) | YES | | NULL | | | all_positions | float | YES | | NULL | | | all_clicks | int(11) | YES | | NULL | | | all_impressions | int(11) | YES | | NULL | | | keyword_count | int(11) | YES | | NULL | | | landing_page | varchar(2083) | YES | | NULL | | | dates | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | profile_id | int(11) | YES | MUL | NULL | | | id | bigint(20) | NO | PRI | NULL | auto_increment | ![]() | Field | Type | Null | Key | Default | Extra | My table is structured like so: +-+-+-+-+-+-+ SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2017' THEN all_positions END) AS `imp (Aug-2017)` SUM(CASE WHEN profile_id=77 and month(dates)='7' and year(dates)='2018' THEN all_positions END) AS `clk (Jul-2018)`, SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2018' THEN all_positions END) AS `clk (Aug-2018)`, SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2017' THEN all_ctr END) AS `imp (Aug-2017)`, SUM(CASE WHEN profile_id=77 and month(dates)='7' and year(dates)='2018' THEN all_ctr END) AS `clk (Jul-2018)`, SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2018' THEN all_ctr END) AS `clk (Aug-2018)`, SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2017' THEN all_clicks END) AS `imp (Aug-2017)`, SUM(CASE WHEN profile_id=77 and month(dates)='7' and year(dates)='2018' THEN all_clicks END) AS `clk (Jul-2018)`, ![]() SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2018' THEN all_clicks END) AS `clk (Aug-2018)`, ![]() SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2017' THEN all_impressions END) AS `imp (Aug-2017)`, SUM(CASE WHEN profile_id=77 and month(dates)='7' and year(dates)='2018' THEN all_impressions END) AS `imp (Jul-2018)`, SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2018' THEN all_impressions END) AS `imp (Aug-2018)`, I have the following query which takes around 20 seconds to return data: select ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |