MySQL COUNT(id) WHERE takes too long

Question

I have an InnoDB table with around 7M rows and size of 4GB. The structure is below(col names are hidden):

When I run the query SELECT COUNT(id) FROM table1 WHERE col7 != '' it takes too long to return the count, somewhere between 50 and 70 seconds. The EXPLAIN EXTENDED report is:

The SELECT count(id) from table1 runs for about 5 seconds, but I need to use the WHERE clause too. My question is are those speeds normal for a server, and what can be done to speed things up, particularly the second query.

The server is running MySQL v5.6.35.

Add an index and avoid the `!=` operator. In your case you can rather do `> ''` it will run much faster — Thomas G, Apr 10 '17 at 09:22
Don't use `COUNT(id)`, use `COUNT(*)`. (This is only part of the solution.) — Rick James, Apr 10 '17 at 17:16

score 1 · Answer 1 · answered Apr 10 '17 at 09:22

To speed things up you need to add a non clustered index to the column you are making the where condition on . This will allow your database to complete the query much faster. To do so try this:

The statement shown here creates an index using the first 10 characters of the name column (assuming that name has a nonbinary string type):

CREATE INDEX part_of_name ON customer (name(10));

resource: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

A prefix index will probably be useless in this context. – Rick James Apr 10 '17 at 17:16 — Rick James, Apr 10 '17 at 17:16

score 1 · Answer 2 · edited May 23 '17 at 12:09

1

this is an evergreen question that was discussed in several discussions.

This is a typical issue of InnoDB engines

Try to have a look to these articles

How to optimize COUNT(*) performance on InnoDB by using index

Why does InnoDB do a full scan for COUNT(*)?

http://kingori.co/minutae/2013/05/mysql-count-innodb/

edited May 23 '17 at 12:09

Community

1
1

answered Apr 11 '17 at 08:23

Maurizio Benedetti

3,557
19
26

MySQL COUNT(id) WHERE takes too long

2 Answers2