Can Group By and Count on MySQL be optimized?

Question

I have a 100 million row table on MySQL. I need to count rows for certain ranges and I have the proper indices for filtering rows. Let's say a SELECT statement returns 20000 results, but all I need is the count. Is there other technique in addition to indexing that I can use? Is there another option such as Cassandra that would handle grouping and counting in a faster way?

Here's the table structure:

mysql> desc activity;
+------------------------+---------+------+-----+---------+-------+
| Field                  | Type    | Null | Key | Default | Extra |
+------------------------+---------+------+-----+---------+-------+
| Source                 | text    | YES  | MUL | NULL    |       |
| Customer               | text    | YES  | MUL | NULL    |       |
| Month                  | int(11) | YES  |     | NULL    |       |
| Day                    | int(11) | YES  |     | NULL    |       |
| Year                   | int(11) | YES  | MUL | NULL    |       |
| Time                   | text    | YES  |     | NULL    |       |
| User                   | text    | YES  |     | NULL    |       |
| TimeStamp              | date    | YES  |     | NULL    |       |
| EmailEventType         | text    | NO   |     | NULL    |       |
+------------------------+---------+------+-----+---------+-------+

Indices:

mysql> show index from activity;
+----------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| activity |          1 | idx_activity_customer |            1 | Customer          | A         |      180035 |       64 | NULL   | YES  | BTREE      |         |               |
| activity |          1 | customer_date         |            1 | Customer          | A         |      202831 |       64 | NULL   | YES  | BTREE      |         |               |
| activity |          1 | customer_date         |            2 | Year              | A         |      303263 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | customer_date         |            3 | Month             | A         |      307744 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | customer_date         |            4 | Day               | A         |     1388270 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | dates                 |            1 | Year              | A         |        1286 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | dates                 |            2 | Month             | A         |       20604 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | dates                 |            3 | Day               | A         |      146993 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | timestamp             |            1 | Year              | A         |        1554 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | timestamp             |            2 | TimeStamp         | A         |      119908 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | timestamp_customer    |            1 | Customer          | A         |      188169 |       64 | NULL   | YES  | BTREE      |         |               |
| activity |          1 | timestamp_customer    |            2 | Year              | A         |      261389 |     NULL | NULL   | YES  | BTREE      |         |               |
| activity |          1 | timestamp_customer    |            3 | TimeStamp         | A         |      743716 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+-----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.01 sec)

Query:

mysql> SELECT Customer, 
   User, 
   Source, 
   Count(CASE 
           WHEN ( Year = 2018 
                  AND Week(TimeStamp) = 1 ) THEN Source 
           ELSE NULL 
         END) AS '2018-W1', 
   Count(CASE 
           WHEN ( Year = 2018 
                  AND Week(TimeStamp) = 2 ) THEN Source 
           ELSE NULL 
         END) AS '2018-W2', 
   Count(CASE 
           WHEN ( Year = 2018 
                  AND Week(TimeStamp) = 3 ) THEN Source 
           ELSE NULL 
         END) AS '2018-W3', 
   Count(CASE 
           WHEN ( Year = 2018 
                  AND Week(TimeStamp) = 4 ) THEN Source 
           ELSE NULL 
         END) AS '2018-W4' 
FROM   activity 

WHERE  customer LIKE 'jones%' 
       AND ( ( Year = 2018 
               AND Week(TimeStamp) = 1 ) 
              OR ( Year = 2018 
                   AND Week(TimeStamp) = 2 ) 
              OR ( Year = 2018 
                   AND Week(TimeStamp) = 3 ) 
              OR ( Year = 2018 
                   AND Week(TimeStamp) = 4 ) ) 
       AND Source IN ( 'online', 'other' ) 
GROUP  BY Source, 
          User 
ORDER  BY Customer, 
          Source; 
+-------------------------+-----------------------------------+----------+---------+---------+---------+---------+
| Customer                | User                              | Source   | 2018-W1 | 2018-W2 | 2018-W3 | 2018-W4 |
+-------------------------+-----------------------------------+----------+---------+---------+---------+---------+
| Jones corporation       | [email protected]               | OTHER    |      87 |      51 |      75 |      20 |
| Jones corporation       | [email protected]           | OTHER    |     125 |      98 |     115 |      62 |
| Jones corporation       | [email protected]        | OTHER    |      30 |       0 |       0 |       0 |

...
Truncated
...

| Jones cpa               | [email protected]           | ONLINE   |       0 |       0 |       0 |      18 |
| Jones cpa               | [email protected]             | ONLINE   |       0 |       0 |       0 |     225 |
+-------------------------+-----------------------------------+----------+---------+---------+---------+---------+
241 rows in set (9 min 10.93 sec)

So basically the optimizer is selecting and index, but then it takes a long time to calculate only 241 rows. Each User has a small row count of each type. How should the GROUP BY fields be related to the index?

Answer 1

To be more correct SQL syntax you need to add customer to your grouping as you are selecting it out without an aggregate (GROUP BY Customer, Source, User). MySQL lets you get away with this but most SQL engines do not as the meaning is potentially ambiguous.

An index covering all the columns, in the right order, you are filtering in a sargable manner and grouping/ordering on, and also covering those you are outputting or filtering on in a non-sargable manager, will allow it to use only that index. So in this case an index on Customer, Source, User, Year, Timestamp.

AND Source IN ( 'online', 'other' ) may cause even this to be less efficient by causing a partial scan between "online" and "other" and reducing the effectiveness gain of the index being in source,user order (so it is only taking full advantage of the customer ordering and just using the rest to avoid lookups in the main table structure). If this is the case then you may find two queries, one for each value, combined afterwards is more efficient. Without seeing what the query planner does we can't tell you that with any certainty though, include the EXPLAIN output to provide more detail there as suggested by danback's answer.

On the face of it this doesn't seem to me to be a query of the complexity, or over data of the size, that won't be efficient enough using normal relational database structures if appropriately indexed. You might find it performs better with some form of column-store index (a non-clustered compressed column store index in SQL Server) but switching technologies is likely to be overkill here.

Answer 2

Based on these large row numbers I'm making the assumption that an estimate is sufficient.

You could look at the filtered or rows column on an EXPLAIN SELECT count(*) FROM tbl WHERE ref=value to provide an estimate of the total based on the query planner sampling. ref: EXPLAIN OUTPUT

If using mariadb and histograms you might be able to derive an estimate from the raw tables(255 granularity).

Answer 3

Two suggestions for you query:

Count(CASE WHEN ( Year = 2018 AND Week(TimeStamp) = 1 ) 
           THEN Source 
           ELSE NULL 
      END)

can be shortened by:

Count(CASE WHEN ( Year = 2018 AND Week(TimeStamp) = 1 ) 
           THEN 1 
      END)

Probably won't affect performance much though.

WHERE  customer LIKE 'jones%' 
       AND ( ( Year = 2018 
               AND Week(TimeStamp) = 1 ) 
              OR ( Year = 2018 
                   AND Week(TimeStamp) = 2 ) 
              OR ( Year = 2018 
                   AND Week(TimeStamp) = 3 ) 
              OR ( Year = 2018 
                   AND Week(TimeStamp) = 4 ) ) 
       AND Source IN ( 'online', 'other' ) 

is the same as:

WHERE customer LIKE 'jones%'
  AND Year = 2018
  AND Week(TimeStamp) BETWEEN 1 AND 4
  AND Source IN ( 'online', 'other' )  

Is Source, User unique? Unless it is you need to add customer_id to he group by clause, or you might end up with incorrect results.

Week(Timestamp) is likely a culprit, can you add a generated column for week and index that? Like:

CREATE INDEX ... ON activity (Year, WEEK, source, customer); 

A compromise may be to change the predicate to:

WHERE customer LIKE 'jones%'
  AND Year = 2018
  AND Month = 1
  AND Source IN ( 'online', 'other' )  

with an index like:

CREATE INDEX ... ON activity (Year, Month, source, customer);

Speaking of indexes, idx_activity_customer is covered by customer_date so you probably can get rid of that.

Another opportunity is to determine in advance when week 4 ends and change the where clause to:

WHERE timestamp between '2018-01-01-00.00.00' 
                    and ... -- timestamp that corresponds to end of week 4

Answer 4

Possibly the main performance problem stems from splitting up a DATETIME into multiple columns.

Instead, reformulate the WHERE clause so that it looks something like

WHERE TimeStamp >= ...
  AND TimeStamp  < ...

where the ellipses are constants or constant expressions. Then, an index containing TimeStamp will be very efficient. (There are caveats.)

The generation of multiple columns such as "2018-W1" is called "pivoting". before adding that to the query, debug and optimize the query with the desired results all in the same column. Then apply "pivot" techniques. This usually involves a subquery.

First, do the bulk of the work more efficiently in the subquery. Then do the pivoting, which will take an insignificant amount of time. By trying to put both together, you ended up with OR clauses which do not Optimize well.

Some minor optimizations:

• Get rid of Year, etc, and keep only TimeStamp. This will significantly shink the table and the indexes.
• Use VARCHAR of a sensible size instead of TEXT.
• INT always takes 4 bytes, consider using smaller datatypes, such as TINYINT UNSIGNED (1 byte), when appropriate.

(And please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.)

Another Optimization is to make the list of columns identical between GROUP BY and ORDER BY. (What you have is too cryptic for me to provide a specific suggestion.)

"but all I need is the count" -- This can be made more efficient by having an INDEX with all the relevant columns. And put the columns in the optimal order.