Re: table partitioning AWS MySQL - InnoDB

[Øystein Grøvlen]

Hi,

I doubt that partitioning will help you much since MySQL will only scan one partition at a time.

10 minutes sounds like a lot. However, it depends on how large your rows are. What is the total table size?

What version of MySQL are you using? There was a change for early versions of 5.7 that would always use a table scan for the counting even if a much shorter index was present. This was reverted in MySQL 5.7.18.

Do you need an exact count? If an estimate is sufficient, you could try
SELECT n_rows FROM mysql.innodb_table_stats WHERE table_name=<table_name>;

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway