title: Slow COUNT(*) with JSON_SEARCH
body: <p>I have a table that stores tags in the JSON format for each page. Current table has about 1,000,000 records. 
  When I run</p>
  
  <pre><code>SELECT COUNT(*) FROM alltags WHERE tagtype = 'pages' AND JSON_SEARCH(tags, 'one', 'Hampden') IS NOT NULL;
  </code></pre>
  
  <p>the query takes about 20 seconds</p>
  
  <pre><code>+----------+
  | COUNT(*) |
  +----------+
  |    23500 |
  +----------+
  1 row in set (19.20 sec)
  </code></pre>
  
  <p>The query is slow regardless if I use InnoDB or MyISAM engine.
  I got indexes for tagtype, tags and tagtype with tags columns. I need the exact number of results for pagination. Is there a way I can speed up the SELECT COUNT query? Selecting actual data is pretty instant.</p>
  
  <pre><code>SELECT id FROM alltags WHERE tagtype = 'pages' AND JSON_SEARCH(tags, 'one', 'Hampden') IS NOT NULL LIMIT 0,10;
  
  +------+
  | id   |
  +------+
  | 5072 |
  | 5075 |
  | 5078 |
  | 5081 |
  | 5084 |
  | 5087 |
  | 5090 |
  | 5093 |
  | 5096 |
  | 5099 |
  +------+
  10 rows in set (0.01 sec)
  </code></pre>
  
  <p>These are the indexes</p>
  
  <pre><code>SHOW INDEX FROM alltags;
  
  +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | alltags |          0 | PRIMARY   |            1 | id          | A         |      896649 |     NULL | NULL   |      | BTREE      |         |               |
  | alltags |          1 | tagtype   |            1 | tagtype     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
  | alltags |          1 | keyword   |            1 | keyword     | A         |          33 |     NULL | NULL   |      | BTREE      |         |               |
  | alltags |          1 | tags      |            1 | tags        | A         |       89665 |     NULL | NULL   | YES  | BTREE      |         |               |
  +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </code></pre>
  
  <p>EXPLAIN results:</p>
  
  <p>Select data</p>
  
  <pre><code> EXPLAIN SELECT id FROM alltags WHERE tagtype = 'pages' AND JSON_SEARCH(tags, 'one', 'Hampden') IS NOT NULL;
  
  +----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------------+
  | id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra       |
  +----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------------+
  |  1 | SIMPLE      | alltags | NULL       | ref  | tagtype       | tagtype | 7       | const | 892372 |   100.00 | Using where |
  +----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)
  </code></pre>
  
  <p>Select count</p>
  
  <pre><code> EXPLAIN SELECT COUNT(*) FROM alltags WHERE tagtype = 'pages' AND JSON_SEARCH(tags, 'one', 'Hampden') IS NOT NULL;
  
  +----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------------+
  | id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra       |
  +----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------------+
  |  1 | SIMPLE      | alltags | NULL       | ref  | tagtype       | tagtype | 7       | const | 892372 |   100.00 | Using where |
  +----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)
  </code></pre>