Hello. I start to write a simple Database management tool and i want
to display the number of rows in a table. Unfortunately MySQL is
extremely slow on this "SELECT count(*) FROM table" statements. Looks
like a full table scan for me. Is there any other way to retrieve this
information?

On Dec 22, 9:55 am, llothar <llot...@web.de> wrote:
> Hello. I start to write a simple Database management tool and i want
> to display the number of rows in a table. Unfortunately MySQL is
> extremely slow on this "SELECT count(*) FROM table" statements. Looks
> like a full table scan for me. Is there any other way to retrieve this
> information?

What engine are you using? I use MyISAM almost exclusively and I can
assure you that this engine does not need a table scan to return the
number of records in a table. I did a select count(*) on a MyISAM
table with just over 20 million rows in .72 seconds on my slow
laptop. The number of records is part of the table's metadata.

On Sat, 22 Dec 2007 06:55:07 -0800 (PST), llothar
<llothar@web.de> wrote:

>Hello. I start to write a simple Database management tool and i want
>to display the number of rows in a table. Unfortunately MySQL is
>extremely slow on this "SELECT count(*) FROM table" statements. Looks
>like a full table scan for me. Is there any other way to retrieve this
>information?

Does your table have a PRIMARY KEY ?
--
( Kees
)
c[_] Nostalgia. Sure ain't what it used to be.... (#26)

SigPower@gmail.com wrote:
> On Dec 22, 9:55 am, llothar <llot...@web.de> wrote:
>> Hello. I start to write a simple Database management tool and i want
>> to display the number of rows in a table. Unfortunately MySQL is
>> extremely slow on this "SELECT count(*) FROM table" statements. Looks
>> like a full table scan for me. Is there any other way to retrieve this
>> information?
>
> What engine are you using? I use MyISAM almost exclusively and I can
> assure you that this engine does not need a table scan to return the
> number of records in a table. I did a select count(*) on a MyISAM
> table with just over 20 million rows in .72 seconds on my slow
> laptop. The number of records is part of the table's metadata.

MyISAM has a record count, so COUNT(*) is cheap. InnoDB tables
do not, so COUNT(*) there can be expensive. There's the issue that
"number of records" is non-trivial to measure when transactions are
in progress.

John Nagle