CODEWITHSUNDEEP
mysqldatabaseperformance
user4965214
user4965214

Reputation:

MySQL COUNT(id) WHERE takes too long

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

table structure

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: enter image description here

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.

Upvotes: 0

Views: 2555

Answers (2)

Maurizio Benedetti
Maurizio Benedetti

Reputation: 3577

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/

Upvotes: 1

Jaafar Barek
Jaafar Barek

Reputation: 420

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

Upvotes: 1

Related Questions