CODEWITHSUNDEEP
mysqlsqlperformanceinnodb
Sanchit
Sanchit

Reputation: 2260

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

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.

Upvotes: 6

Views: 14077

Answers (1)

aidan
aidan

Reputation: 9576

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.

Upvotes: 13

Related Questions