1

Is there any idea to optimize this sql that it takes several minutes to count 11M rows ?Any ideaes, I will be very grateful.

mysql version : 5.7.15-log

explain SELECT count(id) FROM consumer.class:

it responsed

Select tables optimized away

(not use any index ,It seems that the mysql querier is automatically optimized,but it is too slow!!) then i use force index(primary),I get the same result,If i want use force index,I must add where id > 0 ,Run with the sql of

SELECT count(`id`) FROM `consumer`.`class` force INDEX (`id`)

will not use the index too,It is no effect,only like this can use the force index:

enter image description here

Even if the use of the index, the query also takes a few minutes, and sometimes can not even get mysql return. This is unacceptable

show profile(this picture is reduced to 200,000 rows results,31M can not get mysql's response): enter image description here

I found that "Sending data" operate use a lot of time,But I don't know how to optimize.

Community
  • 1
  • 1
Anteoy
  • 31
  • 4
  • Is it any better if you use `COUNT(*)` instead of `COUNT(id)`? – Barmar Sep 22 '17 at 08:12
  • @Barmar no beeter yet,The execution time is almost the same,It seems that the mysql querier is automatically optimized with * and others – Anteoy Sep 22 '17 at 08:14
  • InnoDB doesn't cache the table size. The only way for it to do `COUNT(*)` is by scanning the entire table. – Barmar Sep 22 '17 at 08:15
  • Yes,I know this, if using count, maybe myISAM behaves better in this case, but it does not support the transaction.I would like to know there is any other idea for this? – Anteoy Sep 22 '17 at 08:20
  • I gave you a link to another question that has answers. – Barmar Sep 22 '17 at 08:22

0 Answers0