[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-12
8
Medium Priority
?
1,649 Views
Last Modified: 2010-08-05
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.
0
Comment
Question by:macker-
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 29

Expert Comment

by:fibo
ID: 13993535
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
 
LVL 9

Expert Comment

by:e-tsik
ID: 14008105
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
 
LVL 29

Expert Comment

by:fibo
ID: 14008452
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 7

Author Comment

by:macker-
ID: 14031219
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
 
LVL 9

Expert Comment

by:e-tsik
ID: 14033660
You mean that even
select count(*) from col_id;
Takes 40 minutes to execute?
0
 
LVL 29

Expert Comment

by:fibo
ID: 14044327
and what gives
   select count(col_id) from table_name;
and
   select count(col_id) from table_name force index(col_id);
?
0
 
LVL 4

Accepted Solution

by:
k141 earned 1500 total points
ID: 14065641
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
 
LVL 7

Author Comment

by:macker-
ID: 14117019
sigh.  okay, guess there's no solution for this one... but that does answer my question (i.e. there is no solution).
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question