How can I make CASE WHEN query faster?

Question

I have the following query which takes around 20 seconds to return data:

    select
        landing_page,
        SUM(CASE WHEN profile_id=77 and month(dates)='8' and year(dates)='2018' THEN all_impressions END) AS `imp (Aug-2018)`,
        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)='2017' THEN all_impressions END) AS `imp (Aug-2017)`,
        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)='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)='2017' THEN all_clicks END) AS `imp (Aug-2017)`,
        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)='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)='2017' THEN all_ctr END) AS `imp (Aug-2017)`,
        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)='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)='2017' THEN all_positions END) AS `imp (Aug-2017)`
    from
        landing_pages_v3
    where
        profile_id=77
    group by
        landing_page
    order by
        all_impressions desc
    limit 10

My table is structured like so:

+---------------------+---------------+------+-----+-------------------+-----------------------------+
| Field               | Type          | Null | Key | Default           | Extra                       |
+---------------------+---------------+------+-----+-------------------+-----------------------------+
| id                  | bigint(20)    | NO   | PRI | NULL              | auto_increment              |
| profile_id          | int(11)       | YES  | MUL | NULL              |                             |
| dates               | timestamp     | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| landing_page        | varchar(2083) | YES  |     | NULL              |                             |
| keyword_count       | int(11)       | YES  |     | NULL              |                             |
| all_impressions     | int(11)       | YES  |     | NULL              |                             |
| all_clicks          | int(11)       | YES  |     | NULL              |                             |
| all_ctr             | float         | YES  |     | NULL              |                             |
| all_positions       | float         | YES  |     | NULL              |                             |
| mobile_impressions  | int(11)       | YES  |     | NULL              |                             |
| mobile_clicks       | int(11)       | YES  |     | NULL              |                             |
| mobile_ctr          | float         | YES  |     | NULL              |                             |
| mobile_positions    | float         | YES  |     | NULL              |                             |
| tablet_impressions  | int(11)       | YES  |     | NULL              |                             |
| tablet_clicks       | int(11)       | YES  |     | NULL              |                             |
| tablet_ctr          | float         | YES  |     | NULL              |                             |
| tablet_positions    | float         | YES  |     | NULL              |                             |
| desktop_impressions | int(11)       | YES  |     | NULL              |                             |
| desktop_clicks      | int(11)       | YES  |     | NULL              |                             |
| desktop_ctr         | float         | YES  |     | NULL              |                             |
| desktop_positions   | float         | YES  |     | NULL              |                             |
+---------------------+---------------+------+-----+-------------------+-----------------------------+

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).

This query is used to load a table to display data to users so needs to be loading within 3 seconds, ideally.

The current table size is closing in on 15 million rows.

How can I make this faster?

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.

Version info:

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.53              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.53-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

UPDATE As per comments from Akina, I've got it to around 6 seconds (after caching) with the following:

        select
            landing_page,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2018' THEN all_impressions END) AS `imp (Aug-2018)`,
            SUM(CASE WHEN month(dates)='7' and year(dates)='2018' THEN all_impressions END) AS `imp (Jul-2018)`,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2017' THEN all_impressions END) AS `imp (Aug-2017)`,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2018' THEN all_clicks END) AS `clk (Aug-2018)`,
            SUM(CASE WHEN month(dates)='7' and year(dates)='2018' THEN all_clicks END) AS `clk (Jul-2018)`,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2017' THEN all_clicks END) AS `imp (Aug-2017)`,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2018' THEN all_ctr END) AS `clk (Aug-2018)`,
            SUM(CASE WHEN month(dates)='7' and year(dates)='2018' THEN all_ctr END) AS `clk (Jul-2018)`,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2017' THEN all_ctr END) AS `imp (Aug-2017)`,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2018' THEN all_positions END) AS `clk (Aug-2018)`,
            SUM(CASE WHEN month(dates)='7' and year(dates)='2018' THEN all_positions END) AS `clk (Jul-2018)`,
            SUM(CASE WHEN month(dates)='8' and year(dates)='2017' THEN all_positions END) AS `imp (Aug-2017)`
        from
            landing_pages_v3
        where
            profile_id=77 and month(dates) in ('7', '8') and year(dates) in ('2017', '2018')
        group by
            landing_page
        order by
            all_impressions desc
        limit 10

Answer 1

An even better solution is to add to a Summary Table every month. This would speed the 'report' up -- perhaps to well under 1 second.

Also, something needs to be done about the terribly long landing_page. Probably it should be normalized and replaced by an id. (You should do this anyway, for any solution -- to save lots of space, hence some speed.)

It would have about 7 columns: PRIMARY KEY(profile_id, landing_page_id, yyyymm) and sums stored in imps, clicks, ctrs, positions:

INSERT INTO summary_table
    SELECT    profile_id,
              landing_page_id,
              LEFT(dates, 7) AS yyyymm,
              SUM(all_impressions) AS imps,
              SUM(all_clicks)      AS clicks,
              SUM(all_ctrs)        AS ctrs,
              SUM(all_positions)   AS positions
          FROM landing_pages_v3
          WHERE dates >= '2018-08-01'     -- start of last month
            AND dates  < '2018-08-01' + INTERVAL 1 MONTH
          GROUP BY profile_id,
                   landing_page_id,
                   yyyymm;

Then the report uses the summary table instead of the subqueries in my other Answer.

Answer 2

The final variant may be:

select
    landing_page,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-08-01 00:00:00' and dates < '2018-09-01 00:00:00' THEN all_impressions END) AS `imp (Aug-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-07-01 00:00:00' and dates < '2018-08-01 00:00:00' THEN all_impressions END) AS `imp (Jul-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2017-08-01 00:00:00' and dates < '2017-09-01 00:00:00' THEN all_impressions END) AS `imp (Aug-2017)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-08-01 00:00:00' and dates < '2018-09-01 00:00:00' THEN all_clicks END) AS `clk (Aug-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-07-01 00:00:00' and dates < '2018-08-01 00:00:00' THEN all_clicks END) AS `clk (Jul-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2017-08-01 00:00:00' and dates < '2017-09-01 00:00:00' THEN all_clicks END) AS `imp (Aug-2017)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-08-01 00:00:00' and dates < '2018-09-01 00:00:00' THEN all_ctr END) AS `clk (Aug-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-07-01 00:00:00' and dates < '2018-08-01 00:00:00' THEN all_ctr END) AS `clk (Jul-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2017-08-01 00:00:00' and dates < '2017-09-01 00:00:00' THEN all_ctr END) AS `imp (Aug-2017)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-08-01 00:00:00' and dates < '2018-09-01 00:00:00' THEN all_positions END) AS `clk (Aug-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2018-07-01 00:00:00' and dates < '2018-08-01 00:00:00' THEN all_positions END) AS `clk (Jul-2018)`,
    SUM(CASE WHEN profile_id=77 and dates >= '2017-08-01 00:00:00' and dates < '2017-09-01 00:00:00' THEN all_positions END) AS `imp (Aug-2017)`
from
    landing_pages_v3
where
      (profile_id=77 and dates >= '2017-08-01 00:00:00' and dates < '2017-09-01 00:00:00')
    or
      (profile_id=77 and dates >= '2018-07-01 00:00:00' and dates < '2018-09-01 00:00:00')
group by
    landing_page
order by
    all_impressions desc
limit 10

Additionally: if all_impressions field value is NOT the same for all records with the same landing_page value, your ORDER BY all_impressions desc is close to ORDER BY RAND()...

Answer 3

Your query is slow not because of CASE. It's a simple calculation and adds about to nothing to CPU utilization. The problem is a temporary table and need to sort it before sending the final result to a client.

Add index (profile_id, landing_page, all_impressions) and post an EXPLAIN if the query is still slow.

Answer 4

1. Build 3 queries, one for each month; it will have 4 SUMs.
2. JOIN those together to do the pivoting.

Something like ...

SELECT  landing_page, 
        curr.imps   AS `imp (Aug-2018)`,
        lastmo.imps AS `imp (Jul-2018)`,
        lastyr.imps AS `imp (Aug-2017)`,
        curr.clks   AS `clk (Aug-2018)`,
        lastmo.clks AS `clk (Jul-2018)`,
        lastyr.clks AS `clk (Aug-2017)`,
        ...
    FROM ( SELECT landing_page,
                  SUM(all_impressions) AS imps,
                  SUM(all_clicks)      AS clks,
                  ...
              FROM landing_pages_v3
              WHERE profile_id = 77
                AND dates >= '2018-08-01'
                AND dates  < '2018-08-01' + INTERVAL 1 MONTH
              GROUP BY landing_page )  AS curr
    JOIN ( SELECT ... 2018-07-01 ... ) AS lastmo  USING (landing_page)
    JOIN ( SELECT ... 2017-08-01 ... ) AS lastyr  USING (landing_page)
    ORDER BY 2;

And have this composite index (in this order):

INDEX(profile_id, dates)

That index, with this formulation, will lead to touching only the rows needed. Virtually any other formulation will require reading, but ignoring, lots of other rows. Reading too many rows is the real performance killer.

Since all_impressions is not really available to ORDER BY, I changed to SUM(all_impressions) for the current month. Perhaps this was your intent??

Note that the reformulation of the dates range avoids dealing with Dec/Jan.

I thought about doing UNION, but that got somewhat messy.

Caveat: You probably need at least 5.6 so that the "derived tables" (the 3 subqueries) will be 'materialized'. If you are working with an older version, then consider building three temp tables.

Unfortunately, no index can contain landing_page because it is so big. Does it need to be 2083 characters? What is the CHARACTER SET? (SHOW CREATE TABLE is more descriptive than DESCRIBE.)

Answer 5

Because MySQL can't really pivot, just do it like this and use it from the app.

SELECT
  landing_page,
  EXTRACT(YEAR_MONTH FROM dates),
  count(all_impressions) AS all_impressions,
  count(all_clicks) AS all_clicks,
  count(all_ctr) AS all_ctr,
  count(all_positions) AS all_positions
FROM landing_pages_v3
WHERE profile_id=77
  AND dates BETWEEN '2017-07-01' AND '2018-08-01'
  AND month(dates) IN (7,8)
GROUP BY landing_page, EXTRACT(YEAR_MONTH FROM dates)

Answer 6

Worth noting that if you know a value will appear often in your case matching, you should try to put it towards the start of the CASE statement. e.g. I was reading 1.25 million records using CASE to check one column which on over 1 million rows had a NULL value. Checking for NULL as the first clause (out of eight) of the CASE, made it run much faster, because once a matching value is found, it drops out of the CASE statement immediately it has done whatever action occurs in that clause - the other (in my case seven) clauses are ignored. By not explicitly checking for NULL, it had to go through each of the 8 clauses on the million rows where NULL appeared.