select count(0) from table; running extremely slow despite index

This is running on a dl380 g3, 73gb drives in raid 0+1, dual xeon 2.8's and 6gb of RAM.

mysql> show index from table_name;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| table_name |          0 | PRIMARY  |            1 | col_id  | A         |      436708 |     NULL | NULL   |      | BTREE      |         |

| table_name               | InnoDB |       9 | Dynamic    | 477928 |          41712 | 19935526912 |            NULL |            0 |         0 |           NULL | 2005-05-12 17:45:40 | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=100000000 avg_row_length=10500 | Comment.; InnoDB free: 19983360 kB                           |

From slow-queries.log:
# Query_time: 440  Lock_time: 0  Rows_sent: 1  Rows_examined: 515470
select count(0) from col_id force index(Primary);

This is well beyond my scope as far as MySQL goes, so I have no real idea of what I'm talking about on the SQL side... just bits and pieces, both from what I've read and what I'm being told.  Someone left a note indicating that they did not believe this was using the index, though EXPLAIN does indicate that it is, and as you can see from the slow-queries.log I tried running it with force index.

This query took over 40 minutes to run at one point in testing, then swap was disabled, and run time dropped down to around 4 minutes.  That still seems like an inordinate amount of time to run a count against an index, but see above disclaimer about my knowledge of such things.

Can anyone offer suggestions as to why this is taking so long, where I should be looking, what changes can be made, etc.

So far, I am aware of the index being dropped and re-added, the kernel being upgraded (followed by the server being rebooted), swap space being disabled, and lots of banging-head-against-desk.
LVL 7
macker-Asked:
Who is Participating?
 
k141Commented:
yes, select count(n|*) will do a full table scan because it's innodb. mysql stores the count on myisam tables.

The submitter is right, and this is the way it works. You would have to store the count yourself in another table when doing inserts and deletes within a transaction if you want it to be superfast.
0
 
Bernard S.CTOCommented:
Why count ( zero ) ? (could you explain what you want to achieve?)
some of the questions that will probably be answered by explaining that: Is that on the primary key column? or on some other column? does zero plays a special role? are the numeric values in the columns integer or float? is this column indexed?
0
 
e-tsikCommented:
Hi :-)

select count(0) from col_id force index(Primary);

This query runs on 500,000 rows because MySQL has to run the expression "0" which results in "0", != NULL, hence returns the number of rows on the table.
select count(*) from col_id;
Would return the same result, but much faster because MySQL will then return just the number of rows in the table

Hope it's useful...
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bernard S.CTOCommented:
Macker,
As e-tsik points "but much faster because MySQL will then return just the number of rows in the table" because instead of accessing the full table MySQL would just need to access the index...
0
 
macker-Author Commented:
Hello,

count(*) and count(0) are both doing full table scans.  the count(0) was chosen because the person writing the query feels that this is faster, since 0 is a constant, and that his experience reflects this.  whether or not that's the case, both queries do appear to be doing full table scans.

mysql's documentation seems to suggest this may be an issue with the innodb transactional nature, since it cannot maintain an accurate count of rows in an efficient manner.  I would think that indexes could still be used, since this is a fairly critical aspect of optimal SQL queries, but I don't know enough about innodb's nature to know why this is presenting issues.

this does seem to be specific to innodb, and it's worth mentioning that the innodb database exceeds 50gb in size.. but again, this is a hefty server built to handle it, and we are not seeing performance issues in other aspects.

hoping someone here might have knowledge of innodb specifically and why this may be having issues.  there is differences in run-time on first query vs. subsequent queries, but this appears to be a benefit of disk caching by the o/s, rather than any action by mysql itself.  the explain statement does indicate it should be using the index.

the next step is going to be to contact mysql for commercial support, although the idea of moving the database to oracle is also being discussed, which may be appropriate for other reasons.
0
 
e-tsikCommented:
You mean that even
select count(*) from col_id;
Takes 40 minutes to execute?
0
 
Bernard S.CTOCommented:
and what gives
   select count(col_id) from table_name;
and
   select count(col_id) from table_name force index(col_id);
?
0
 
macker-Author Commented:
sigh.  okay, guess there's no solution for this one... but that does answer my question (i.e. there is no solution).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.