3

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
5
  • Add the conditions to the WHERE which selects the records only for Jul-2017 and Jul..Aug-2018. 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()...
    – Akina
    Sep 3, 2018 at 11:00
  • Replace a condition like and month(dates)='8' and year(dates)='2018' with range pair of conditions and dates >= '2018-08-01 00:00:00' and dates < '2018-09-01 00:00:00'. Replace the condition in WHERE with the same variant (more-less range conditions - it will give 2 pairs of conditions linked via OR) which do not use functions.
    – Akina
    Sep 3, 2018 at 11:11
  • You should look at offsetting a lot of your CASE by using separate table and do joins. Your query certainly needs to be changed for each new months, so this does not seem to scale. That would give you far more flexibility. Sep 3, 2018 at 23:12
  • Be curious to know how my query runs. Oct 2, 2018 at 5:50
  • CASE WHEN is a red herring. In many situations, it is a good idea. In this query, other things are much worse for performance, and fixing them happens to obviate the need for CASE WHEN.
    – Rick James
    Oct 2, 2018 at 5:53

6 Answers 6

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.

0

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

5
  • What do you mean by `close to ORDER BY RAND()'
    – Adders
    Sep 3, 2018 at 11:24
  • Also, in this instance would a composite index be a good idea?
    – Adders
    Sep 3, 2018 at 11:24
  • would a composite index be a good idea? Yes, by (profile_id,dates) (it seems it already exists). What do you mean by close to ORDER BY RAND() If there are different values of all_impressions for the same landing_page a random value from this values list will be taken for ordering.
    – Akina
    Sep 3, 2018 at 11:29
  • The OR kills the ability to use that particular composite index.
    – Rick James
    Oct 2, 2018 at 5:25
  • I've added the id from the landing_pages_v3 table into the select statement but it seems to grab the id from any of the months and not specifically the one I'm after - any way round this?
    – Adders
    Oct 6, 2018 at 23:45
0

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.

2
  • not with dates?
    – Adders
    Sep 3, 2018 at 15:07
  • landing_page is too big; all_impressions is just one of 4; dates is useless when hidden inside MONTH(). Furthermore, the temp table is only a few dozen rows long, so not a big deal.
    – Rick James
    Oct 2, 2018 at 5:37
0
  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.)

0

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.