10

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.

Ratan Uday Kumar
  • 5,738
  • 6
  • 35
  • 54
M Schenkel
  • 6,294
  • 12
  • 62
  • 107
  • 7
    Please consider posting the entire output of `EXPLAIN` into your question. – Benjamin Gruenbaum Feb 02 '14 at 23:57
  • 2
    + the table schema + how many rows (abs value and in %) meet the given condition? – zerkms Feb 02 '14 at 23:58
  • 2
    What is the number returned by the `count` query? – Martin Smith Feb 03 '14 at 00:04
  • 3
    Do you run the queries through Workbench, PHPMyAdmin, some other tool or using command line? – ypercubeᵀᴹ Feb 03 '14 at 00:07
  • The count returns 150, and the client will return all 150 of those records. See the answer below. Someone indicated that SELECT COUNT(*) result in full table scans. If this is true, then this would certainly explain it. – M Schenkel Feb 03 '14 at 00:46
  • @BenjaminGruenbaum - I did. See the UPDATE 2 – M Schenkel Feb 03 '14 at 01:35
  • @ypercube - We are running it through ODBC windows based tool. It uses ODBC drivers. – M Schenkel Feb 03 '14 at 01:37
  • Possible duplicate of [“SELECT COUNT(*)” is slow, even with where clause](http://stackoverflow.com/questions/511820/select-count-is-slow-even-with-where-clause?rq=1) – Jivan Feb 03 '14 at 01:52
  • Why are you doing COUNT(*) instead of COUNT(1)? – Random832 Feb 03 '14 at 02:59
  • I think in order to solve your problem you will need to post the queries running against this table along with the explain output of each problem query. Optimizing server settings is a good start, however, individual query performance sounds like your problem now. – Vignesh Kumar A Feb 03 '14 at 03:42
  • How consistent are these timings? How many times have you run them? – Gordon Linoff Feb 03 '14 at 04:18
  • Does adding SQL_NO_CACHE to the select * from query make any differance? eg making it slower? (select SQL_NO_CACHE * from... ) – Jakob Feb 03 '14 at 07:16
  • Could it be possible that when running the `SELECT *` query, the results are already in the cache? If you re-run the `SELECT COUNT(*)`, is it faster also? – Oscar Pérez Feb 03 '14 at 10:40
  • @Random832 - I just tried **select count(1)** and it does the same thing. – M Schenkel Feb 03 '14 at 13:36
  • @GordonLinoff - I iterated a couple times between the two queries. And each time the **select count(*)** was slow, and the **select *** was very fast. I would have almost expected that some sort of caching to take place on subsequent queries. But it appears to not. – M Schenkel Feb 03 '14 at 13:39
  • Could you try something like that `select count(1) from (select * from detail where mainid = 9999)` – dagfr Feb 04 '14 at 08:24
  • 1
    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? – Max Yakimets Feb 04 '14 at 12:15
  • Read http://www.tuxradar.com/practicalphp/9/4/9 to see what I mean – Max Yakimets Feb 04 '14 at 12:22
  • Well what do you expect? By using `count(*)` the query is trying to "count" all of your columns which would take a lot more time than if you'd used only one column, i.e. the primary key `count(detail_id)` – tftd Feb 08 '14 at 21:27
  • What's the structure of the tables? Is 999 a valid key for maintableid (i.e. it's not being cast)? What's the cardinality of maintableid to the size of detailtable? – Lnr Jul 05 '19 at 15:07

6 Answers6

1

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.

Natarajan N Napoleon
  • 369
  • 1
  • 4
  • 9
0

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.
pm_ingram
  • 104
  • 2
0

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
derek.wolfe
  • 1,086
  • 6
  • 11
0
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.

dodzb
  • 379
  • 2
  • 5
0

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.

Mohamed ElKalioby
  • 1,908
  • 1
  • 12
  • 13
-1

Or simply Try 

SELECT count(1) FROM table_name WHERE _condition;

SELECT count('x') FROM table_name WHERE _condition;
Jordy
  • 121
  • 2
  • 13
Rohit
  • 21
  • 1
  • 1
  • 7