slow select count(*)

I am doing a "select count(*) mem_address from mem_address;" on a table with
750,000 rows. It takes over six seconds to return the first time. The
subsequent executions of SQL come back instantaneously because of query
caching.

Any ideas why a count(*) would be slow and how I can speed it up?

Thanks!

On Apr 23, 5:22 pm, "Rajesh Kapur" <rka...@mpr.org> wrote:
> I am doing a "select count(*) mem_address from mem_address;" on a table with
> 750,000 rows. It takes over six seconds to return the first time. The
> subsequent executions of SQL come back instantaneously because of query
> caching.
>
> Any ideas why a count(*) would be slow and how I can speed it up?
>
> Thanks!


Try just counting the primary key field instead:

SELECT COUNT(id) FROM mem_address

The first time response time for "SELECT COUNT(id) FROM mem_address" is the
same as "select count(*) mem_address from mem_address;"
This is an innodb table.

"strawberry" <zac.carey@gmail.com> wrote in message
news:1177355744.077584.210980@b75g2000hsg.googlegr oups.com...
> On Apr 23, 5:22 pm, "Rajesh Kapur" <rka...@mpr.org> wrote:
>> I am doing a "select count(*) mem_address from mem_address;" on a table
>> with
>> 750,000 rows. It takes over six seconds to return the first time. The
>> subsequent executions of SQL come back instantaneously because of query
>> caching.
>>
>> Any ideas why a count(*) would be slow and how I can speed it up?
>>
>> Thanks!
>
>
> Try just counting the primary key field instead:
>
> SELECT COUNT(id) FROM mem_address
>

the most efficient way to do this is have a separate table with the counts in it.
you then put a trigger on the innodb table mem_address that updates the count
table every time there is an insert or delete

Rajesh Kapur schrieb:
> I am doing a "select count(*) mem_address from mem_address;" on a table with
> 750,000 rows. It takes over six seconds to return the first time.

This *should* be near-instantaneous.
Try to ANALYZE TABLE mem_address.

Possibly there is a typo in the SQL you're using; please copy&paste the
slow query both into whatever your frontend is and the message.

Does the table have a primary key? It's possible that InnoDB doesn't
keep track of the number of rows other than in the primary key.

Regards,
Jo

Joachim Durchholz wrote :

>> I am doing a "select count(*) mem_address from mem_address;" on a table with
>> 750,000 rows. It takes over six seconds to return the first time.
>
> This *should* be near-instantaneous.
> Try to ANALYZE TABLE mem_address.
>
> Does the table have a primary key? It's possible that InnoDB doesn't
> keep track of the number of rows other than in the primary key.

AFAIK, even for the PK InnoDB does not keep track of the exact number of
rows. Just make a
"show index from foo" to look at the cardinality of your PK and then a
"select count(1) from foo" and you should not have exaclty the same
result (unless lucky).

For the Original Poster, according to me the delay is caused by the
loading of the index data from disk to memory.

I do have a table ("bar") with around 10 millions of entries. With a
MySQL just restarde. Memory print of MySQL is around 150 MB. I just then do
"select count(1) from bar;"
that take around 60 seconds to execute. At the end of the process, the
MySQL memory print is around 4G, just because the index of the table bar
was loaded into memory. I did, for other reasons, some ANALYZE on this
table without change on this part.

If just afeter that (once the index are into memory) I do again a
"select sql_no_cache count(1) from bar;" it takes only 1 or 2 seconds.
(You should use the "sql_no_cache" statement to avoid MySQL using the
query cache, and get relevant result).

So if you really need this information, and need it quickly, as someone
else state before, explicitly store the number of elements in
"mem_adress" somewhere else. But this number will be more or less
accurate, since with InnoDB engine is transactional and use some
isolation policy. I guess this is why the number of rows per table is
not stored directly by the InnoDB engine unlike MyISAM.

--
Hugo

Hugo schrieb:
> with InnoDB engine is transactional and use some
> isolation policy. I guess this is why the number of rows per table is
> not stored directly by the InnoDB engine unlike MyISAM.

Ah, right, now I recall.
This is actually mentioned in the mysql documentation somewhere (I think
on the driver-specific page for InnoDB).

Regards,
Jo