COUNT(Non-NULL Column) Rewrite Optimization

Scenarios

In SQL semantics, COUNT(column) returns the number of non-NULL values in a column. When the column is defined as NOT NULL, COUNT(column) is equivalent to COUNT(*).

TaurusDB automatically detects this equivalence and rewrites COUNT(non-null column) as COUNT(0) (that is, COUNT(*)). After the rewrite, the query no longer depends on the data of that column, allowing the optimizer to choose an index that does not include the column. The optimizer can use smaller indexes and avoid table lookups. This significantly improves query performance.

Prerequisites

• Only instances running kernel version 2.0.75.260300 or later support the COUNT(non-NULL column) rewrite optimization. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
• The simplify_count_not_null switch must be enabled (it is enabled by default) for the COUNT optimization to take effect.

Constraints

Parameters

How to Use

• Enabling and Disabling the Optimization

  You can enable or disable this feature through the simplify_count_not_null option in the optimizer_switch parameter. For details, see Modifying TaurusDB Instance Parameters.

  You can also enable or disable the COUNT optimization using SQL statements.

  • Check the current status.

    SELECT @@optimizer_switch LIKE '%simplify_count_not_null=on%';

  • Enable the optimization (default).

    SET optimizer_switch='simplify_count_not_null=on';

  • Disable the optimization.

    SET optimizer_switch='simplify_count_not_null=off';

• Check whether the optimization takes effect.

  Use EXPLAIN to check the execution plan. You can determine whether the optimization is applied in the following ways:

  • Warnings information: If the warnings output of EXPLAIN shows count(0), the rewrite has taken effect.
  • Using index: If the Extra column contains Using index, the query is executed using an index scan.

    mysql> CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b(b));
    Query OK, 0 rows affected (0.05 sec)

    mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t1
       partitions: NULL
             type: index
    possible_keys: idx_b
              key: idx_b
          key_len: 5
              ref: NULL
             rows: 1
         filtered: 100.00
            Extra: Using where; Using index
    1 row in set, 1 warning (0.01 sec)

    mysql> show warnings;
    +-------+------+-------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                   |
    +-------+------+-------------------------------------------------------------------------------------------+
    | Note  | 1003 | /* select#1 */ select count(0) AS `COUNT(a)` from `test`.`t1` where (`test`.`t1`.`b` > 1) |
    +-------+------+-------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

    The count(0) entry in the warnings output indicates that COUNT(a) has been rewritten as COUNT(0), and the Using index flag in the Extra column indicates that an index scan is used.

  • You can also use optimizer_trace to check whether the rewrite has occurred.

    mysql> CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b(b));
    Query OK, 0 rows affected (0.07 sec)

    mysql> SET optimizer_trace='enabled=on';
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT COUNT(a) FROM t1 WHERE b > 1;
    +----------+
    | COUNT(a) |
    +----------+
    |        0 |
    +----------+
    1 row in set (0.01 sec)

    mysql> SELECT trace LIKE '% count(0) %' FROM information_schema.optimizer_trace;
    +---------------------------+
    | trace LIKE '% count(0) %' |
    +---------------------------+
    |                         1 |
    +---------------------------+
    1 row in set (0.01 sec)

    The result indicates that the rewrite has occurred.

    mysql> SET optimizer_trace=default;
    Query OK, 0 rows affected (0.00 sec)

Example 1: Single-Column Index Only

CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b(b));
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(5,3),(8,3),(4,-1);
ANALYZE TABLE t1;

• When the optimization is disabled, the optimizer must read the value of column a to determine whether it is NULL. Because the index idx_b does not contain column a, the optimizer must perform a table lookup to fetch the full row to access column a.

  mysql> SET optimizer_switch='simplify_count_not_null=off';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: range
  possible_keys: idx_b
            key: idx_b
        key_len: 5
            ref: NULL
           rows: 1
       filtered: 100.00
          Extra: NULL
  1 row in set, 1 warning (0.00 sec)

• When the optimization is enabled, COUNT(a) is rewritten as COUNT(0) so the optimizer no longer needs to access column a. The optimizer can choose to scan the idx_b index only, without performing a table lookup.

  mysql> SET optimizer_switch='simplify_count_not_null=on';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: index
  possible_keys: idx_b
            key: idx_b
        key_len: 5
            ref: NULL
           rows: 1
       filtered: 100.00
          Extra: Using where; Using index
  1 row in set, 1 warning (0.00 sec)

Example 2: Single-Column Index and Composite Index

Assume that column a in table t1 is defined as NOT NULL and the table contains both a single-column index idx_b and a composite index idx_b_a.

CREATE TABLE t1 (a INT NOT NULL, b INT, KEY idx_b (b), KEY idx_b_a (b, a));
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(5,3),(8,3),(4,-1);
ANALYZE TABLE t1;

• When the optimization is disabled, the optimizer must read column a to determine whether it is NULL. Because idx_b does not include column a, using this index requires a table lookup. To avoid the table lookup, the optimizer chooses the composite index idx_b_a, which allows the query to be executed directly from the index.

  mysql> SET optimizer_switch='simplify_count_not_null=off';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: range
  possible_keys: idx_b,idx_b_a
            key: idx_b_a
        key_len: 5
            ref: NULL
           rows: 4
       filtered: 100.00
          Extra: Using index
  1 row in set, 1 warning (0.00 sec)

• When the optimization is enabled, COUNT(a) is rewritten as COUNT(0) so the optimizer no longer needs to access column a. In this case, the optimizer can choose to scan the smaller single-column index idx_b, avoiding both table lookups and the use of the larger composite index.

  mysql> SET optimizer_switch='simplify_count_not_null=on';
  Query OK, 0 rows affected (0.00 sec)
  mysql> EXPLAIN SELECT COUNT(a) FROM t1 WHERE b > 1 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: range
  possible_keys: idx_b,idx_b_a
            key: idx_b
        key_len: 5
            ref: NULL
           rows: 4
       filtered: 100.00
          Extra: Using index
  1 row in set, 1 warning (0.00 sec)