select count(*) taking considerably longer than select * for same "where" clause?

select count(*) taking long time oracle
select count(*) taking long time sql server
select count(*) taking a long time
select vs select count performance
mysql count slow large table
select count takes long time postgres
innodb count slow
mysql select count slow

I am finding a select count(*) is taking considerably longer than select * for the queries with the same where clause.

The table in question has about 2.2 million records (call it detailtable). It has a foreign key field linking to another table (maintable).

This query takes about 10-15 seconds:

select count(*) from detailtable where maintableid = 999

But this takes a second or less:

select * from detailtable where maintableid = 999

UPDATE - It was asked to specify the number of records involved. It is 150.

UPDATE 2 Here is information when the EXPLAIN keyword is used.

For the SELECT COUNT(*), The EXTRA column reports:

Using where; Using index

KEY and POSSIBLE KEYS both have the foreign key constraint as their value.

For the SELECT * query, everything is the same except EXTRA just says:

Using Where

UPDATE 3 Tried OPTIMIZE TABLE and it still does not make a difference.

For sure

select count(*)

should be faster than

select *

count(*), count(field), count(primary key), count(any) are all the same.

Your explain clearly stateas that the optimizer somehow uses the index for count(*) and not for the other making the foreign key the main reason for the delay.

Eliminate the foreign key.

Why is count(*) slow, when explain knows the answer?, Even if you did select count(id) , it might still take a very long time, unless you in B-Tree indexes, performing a select count(PK_COLUMN) is still a considerable TABLES gives you the same estimate that EXPLAIN gives you. Performing the COUNT(*) on an InnoDB table must scan the table to avoid  As many blog posts mention (“one weird trick to make postgres 50x faster!”) you can guide the planner by rewriting count distinct as the count of a subquery:-- SELECT COUNT(DISTINCT n) FROM items; -- must be rewritten as EXPLAIN SELECT COUNT (*) FROM (SELECT DISTINCT n FROM items) t; Aggregate (cost = 34629. 06.. 34629. 07 rows = 1 width

Try

select count(PRIKEYFIELD) from detailtable where maintableid = 999
  1. count(*) will get all data from the table, then count the rows meaning it has more work to do.
  2. Using the primary key field means it's using its index, and should run faster.

Count(*) query is slower on partitioned table, In short, the issue is that a SELECT COUNT(*) query over the exact same dataset takes longer to However I am noting considerable differences, with the query on the partitioned table always taking longer to execute. Even if you did select count(id), it might still take a very long time, unless you have a secondary index on id (also assuming id is a PRIMARY KEY). Because all data (including Row Data) is stored in B-Tree indexes, performing a select count(PK_COLUMN) is still a considerable amount of IO (needs to reads all data pages). If you have a secondary index on the PK field, it will be able to perform less IO to perform a count.

Thread Necro!

Crazy idea... In some cases, depending on the query planner and the table size, etc, etc., it is possible for using an index to actually be slower than not using one. So if you get your count without using an index, in some cases, it could actually be faster.

Try this:

SELECT count(*) 
  FROM detailtable 
 USING INDEX ()
 WHERE maintableid = 999

COUNT(*) for Innodb Tables, If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and  Why would SELECT COUNT(*) take 24 minutes to run ? Running on a quiet DEV server. No Blocking. Table has 280,000 rows. select count(*) from MyTable

SELECT count(*)

with that syntax alone is no problem, you can do that to any table. The main issue on your scenario is the proper use of INDEX and applying [WHERE] clause on your search. Try to reconfigure your index if you have the chance.

If the table is too big, yes it may take time. Try to check MyISAM locking article.

COUNT(*) vs COUNT(col), And what about “SELECT count(1) FROM fact;” – is that treated the same way as count(*) ? I was taught (15 yrs ago) that count(1) was faster. April  I am finding a select count(*) is taking considerably longer than select * for the queries with the same where clause. The table in question has about 2.2 million records (call it detailtable). It has a foreign key field linking to another table (maintable). This query takes about 10-15 seconds: select count(*) from detailtable where

As the table is 2.2 million records, count can take time. As technically, MySQL should find the records and then count them. This is an extra operation that becomes significant with millions of records. The only way to make it faster is to cache the result in another table and update it behind the scenes.

Aggregate functions in SQL, With COUNT , SUM , AVERAGE , and other functions, SQL will be able to provide most of the analytical functionality we have with Pivot Tables, with considerably more database, just so any errors you make don't take even longer to figure out​. SELECT PdDistrict, COUNT(*) FROM sfpd_incidents GROUP BY PdDistrict. 1. ord_amount against the order is more than 1500, the following SQL statement can be used : SELECT COUNT( * ) as "Number of Rows" FROM orders WHERE ord_amount>1500; Output: Number of Rows ----- 22 Application of COUNT() function . In the subsequent pages, we have discussed how to apply COUNT() with various SQL clauses.

Ask TOM "difference between count(1) and count(*)", Same number of blocks read/written/processed, same cpu times (basically) same elapsed Here is my test result, it show count(*) is much fast than count(1). second, a simple tkprof shows count(*) and count(rowid) are not significantly ¿t is a "users" table, but when i use :select count(*) from users; it takes too long time. SELECT COUNT(*) FROM flight_delays LIMIT 10; The result is: 7275288. The function itself is COUNT, and it says to count the lines using every column (*)… You can change the * to any columns’ name (eg. arrdelay) – and you will get the very same number. Try this: SELECT COUNT(arrdelay) FROM flight_delays LIMIT 10; Right? Same result: 7275288.

Making slow queries fast using composite indexes in MySQL, This post helps you select columns which to index and shows how to So you have inserted preliminary data to your database and run a simple COUNT(*) query to handle millions of rows easily but even your simple query takes This is because MySQL goes through the exact same amount of data for  Jignesh, I have a question on the timing results for the SELECTINTO test with a large data set. Your example shows CPU time at 1499 ms and elapsed time at 489 ms. How can CPU time exceed elapsed time? Parallelization? I noticed also that the CPU time was longer than the INSERT INTO example test of the same large data set. Thanks!

Finding Correlated Rows Using EXISTS or COUNT, Should you always use EXISTS rather than COUNT when checking for with a green wriggly line under SELECT COUNT(*)… , for a violation row from in the orders table for in item with a cost of 500 or greater? Each of these queries gave the same set of rows as output; they all give the correct answer. As pointed out in the article I posted, beginning in SQL Server 2005, the optimizer converts IF (SELECT COUNT)>0 to an EXISTS under the covers, so I would expect those to have the same plan.

Comments
  • Please consider posting the entire output of EXPLAIN into your question.
  • + the table schema + how many rows (abs value and in %) meet the given condition?
  • What is the number returned by the count query?
  • Do you run the queries through Workbench, PHPMyAdmin, some other tool or using command line?
  • May be it's the case of buffered/unbuffered queries? ODBC driver or the client using it set a relatively small buffer size and MySQL quickly fills it with first found rows and do not continue table scan until the client consumes the buffer and requests more? Did you measure only the execution of SELECT * or also the retrieval of all of 150 records?
  • Even the worst query planner should treat COUNT(column), COUNT(*), and COUNT(1) identically.