MySQL query very slow. Count(*) on indexed column

Question

The table is in InnoDB table. Here is some information that might be helpful.

EXPLAIN SELECT COUNT(*) AS y0_ FROM db.table this_ WHERE this_.id IS NOT NULL;

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | this_ | index | PRIMARY       | PRIMARY | 8       | NULL | 4711235 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE db.table;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | bigint(20)   | NO   | PRI | NULL    |       |
| id2          | varchar(28)  | YES  |     | NULL    |       |
| photo        | longblob     | YES  |     | NULL    |       |
| source       | varchar(10)  | YES  |     | NULL    |       |
| file_name    | varchar(120) | YES  |     | NULL    |       |
| file_type    | char(1)      | YES  |     | NULL    |       |
| created_date | datetime     | YES  |     | NULL    |       |
| updated_date | datetime     | YES  |     | NULL    |       |
| createdby    | varchar(50)  | YES  |     | NULL    |       |
| updatedby    | varchar(50)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
10 rows in set (0.05 sec)

The explain query gives me the result right there. But the actual query has been running for quite a while. How can I fix this? What am I doing wrong?

I basically need to figure out how many photos there are in this table. Initially the original coder had a query which checked WHERE photo IS NOT NULL (which took 3hours+) but I changed this query to check the id column as it is a primary key. I expected a huge performance gain there and was expecting an answer in under a second but that seems to not be the case.

What sort of optimizations on the database do I need to do? I think the query is fine but feel free to correct me if I am wrong.

Edit: mysql Ver 14.14 Distrib 5.1.52, for redhat-linux-gnu (x86_64) using readline 5.1

P.S: I renamed the tables for some crazy reason. I don't actually have the database named db and the table in question named table.

Answer 1

How long is 'long'? How many rows are there in this table?

A MyISAM table keeps track of how many rows it has, so a simple COUNT(*) will always return almost instantly.

InnoDB, on the other hand works differently: an InnoDB table doesn't keep track of how many rows it has, and so when you COUNT(*), it literally has to go and count each row. If you have a large table, this can take a number of seconds.

EDIT: Try COUNT(ID) instead of COUNT(*), where ID is an indexed column that has no NULLs in it. That may run faster.

EDIT2: If you're storing the binary data of the files in the longblob, your table will be massive, which will slow things down.

Possible solutions:

1. Use MyISAM instead of InnoDB.
2. Maintain your own count, perhaps using triggers on inserts and deletes.
3. Strip out the binary data into another table, or preferably regular files.