Fast way to do "SELECT count(*) FROM table"

22/12/2007, 14h55

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?

22/12/2007, 15h43

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.

22/12/2007, 19h24

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)

26/12/2007, 04h54

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

22/12/2007, 14h55	#1
llothar Messages: n/a Hébergeur:	*Fast way to do "SELECT count() FROM table"** 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?

22/12/2007, 19h24	#3
Kees Nuyt Messages: n/a Hébergeur:	*Re: Fast way to do "SELECT count() FROM table"** 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)

26/12/2007, 04h54	#4
John Nagle Messages: n/a Hébergeur:	*Re: Fast way to do "SELECT count() FROM table"** 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

22/12/2007, 15h43	#2
SigPower@gmail.com Messages: n/a Hébergeur:	*Re: Fast way to do "SELECT count() FROM table"** 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.

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email