1

I have a database with a Symbol col (among a few others).

If I run:

mysql> explain select count(*) from ABT where Symbol='AFMD';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows    | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+---------+----------+--------------------------+
|  1 | SIMPLE      | ABT   | NULL       | ref  | idx_Symbol    | idx_Symbol | 41      | const | 1042126 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+---------+----------+--------------------------+

it tells me that there are 1042126 rows to check, but then if I run:

mysql> select count(*) from ABT where Symbol='AFMD';
+----------+
| count(*) |
+----------+
|   531383 |
+----------+

tells me that there's only 531383 values.

How is this possible if I indexed the table based on the Symbol col? I.e.:

mysql> show index from ABT;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| ABT   |          0 | PRIMARY    |            1 | ID          | A         |    24583232 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| ABT   |          1 | idx_Symbol |            1 | Symbol      | A         |       40098 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| ABT   |          1 | idx_Time   |            1 | Time        | A         |     2619249 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| ABT   |          1 | idx_Type   |            1 | Type        | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Thanks

Edit: This is after running ANALYZE on the table.

2

There is an interesting option in InnoDB called innodb_stats_persistent_sample_pages.

This setting dictates how many index pages are read in order to guess the cardinality of key combinations. For the PRIMARY KEY, the guess would be the row count.

Depending on the shape of the BTREE, the number of sample pages read will either be too low or too high. The default value is usually sufficient, thus never needing any adjustment.

Even with the cardinality of the idx_Symbol being a factor of 4 (40098 times 4 = 160392) against the query, the ratio for rows to key combinations can usually be trusted without question. The shape of the BTREE is one thing, the pages traversed is another.

Look at your query

explain select count(*) from ABT where Symbol='AFMD';

The index pages sampled for this came up with a number almost twice the number of rows in the EXPLAIN plan but is not actual row count. Try running the explain on the query without the index (using IGNORE INDEX) and see what numbers you get.

  • Thanks, I see. With ignore index it actually gives 24593688 while the full table is 24691366 rows. – vakker Aug 5 at 18:16
3

The difference between what EXPLAIN output shows and the actual number of rows returned by the query means your table statistics are out of date. The explain plan displays the estimated number of rows it thinks the engine will need to read. As the manual says,

MySQL explains how it would process the statement

(emphasis mine). Strictly speaking, the database engine cannot possibly know how many rows it needs to process until it actually processes all of them.

You can run the ANALYZE TABLE command to update statistics; EXPLAIN might be able to better estimate how many rows would be read.

  • 2
    And even after ANALYZE, some of the statistics will be way off. – Rick James Aug 3 at 3:02
  • I did run ANALYZE on the table and this is after that. So maybe the 1042126 corresponds to the size of a whole branch in the BTREE? This combined with the filtered 100.00 seems very suboptimal. Any suggestions on how to make this more efficient? – vakker Aug 4 at 8:46
  • @vakker Why are you so fixated on the cardinality estimate? If you are concerned about the query performance, you should ask a different question, providing relevant details. Even if the optimizer suddenly comes up with a better estimate, it won't necessarily mean better response time. – mustaccio Aug 4 at 17:03
  • @mustaccio I'm fixated on it because I'm trying to understand better what's happening and why it gives something counter-intuitive. – vakker Aug 5 at 18:18

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.