CODEWITHSUNDEEP
mysqlcountinnodb
Ashraf Amayreh
Ashraf Amayreh

Reputation: 163

My innodb table returns 0 for count(*)

I'm doing this query at the mysql command line (not through any kind of code)

select count(*) from books where
publisher_id = 46 AND deleted = 0 AND status_id = 3;

Returns 0 (sometimes), but remove the count, it has many results.

What could be causing this? I'm throwing this query at the command line (not through any kind of programming layer)

Explaining count query gives:

Using intersect(IDX_cms_books_publisher_id,IDX_cms_books_status_id,IDX_cms_books_deleted); Using where; Using index

Upvotes: 1

Views: 175

Answers (2)

Rick James
Rick James

Reputation: 142433

You can both avoid the bug and make the query faster by adding a 'composite' index:

INDEX(publisher_id, deleted, status_id)

(The columns may be in any order.)

If PARTITION BY HASH is involved, perhaps bug 70588 is involved. It was fixed in 5.5.36, 5.6.16, 5.7.4.

Upvotes: 0

Ashraf Amayreh
Ashraf Amayreh

Reputation: 163

The problem seems to be a bug with mysql. It's caused by index merge intersection.

You can either set it per session SET SESSION optimizer_switch="index_merge_intersection=off";

Or set it globally in your my.cnf

[mysqld] optimizer_switch=index_merge_intersection=off

Bug report filed here

http://bugs.mysql.com/bug.php?id=81031

Upvotes: 4

Related Questions