[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-java
Subject:    RE: How much faster?
From:       Niklaus Keller <keller () huygens ! math-lab ! unibas ! ch>
Date:       2000-07-31 18:12:45
[Download RAW message or body]


There is a remark in the mySQL Documentation in chapter 12.5.3 :

COUNT(*) on a single table without a WHERE is retrieved directly from the 
table information. This is also done for any NOT NULL expression when used
with only one table.

if you do a 'SELECT * FROM table_a' and count the rows with a while loop
in Java then all the content of the result of the SELECT is transmitted
from the MySQL-Server to your Java-Client, i think. This is at least
linear in the number of rows (except compression and variable length of
data i.e. VARCHAR or so), but i'm not realy sure about this.

If you just interested in the number of rows, then i would do
a 'SELECT COUNT(*) ...'. But if you are interested to get the number
of rows that you retrieve from a 'SELECT * FROM ...',
i would not do an other 'SELECT COUNT(*) FROM ...' first, because
between this SELECT and the following SELECT somebody
could insert or delete some rows, and then you have a problem.

So if you want to store the ResultSet use the Vector Class or so, then
you don't have to know how large the ResultSet is. This is better
than a Array, where you have to know the size first.

May it help :-)

regards
  Niklaus


On Tue, 1 Aug 2000, Gary Bentley wrote:

> Unfortunately my DB doesn't go up that high.  But I've got a feeling that
> it's not linear, i.e. maybe MySQL keeps a count of the number of records in
> the table so that COUNT(*) will always be 9, but SELECT would then ramp as
> the number of records ramp...
> 
> I might populate a DB with a lot of small records and then see what
> happens...
> 
> The really interesting one would be checking for a specific record, i.e. how
> does that increase with size, also is it dependent on the type of key.  I'm
> sure that there must be information on this stuff somewhere, I just don't
> have time to look!
> 
> G...
> 
> -----Original Message-----
> From: LEBLANC, CLAUDE [mailto:claude.leblanc@bell.ca]
> Sent: Monday, July 31, 2000 6:15 PM
> To: Gary Bentley
> Cc: Anders Ericsson; java@lists.mysql.com
> Subject: Re: How much faster?
> 
> 
> Gary Bentley wrote:
> >
> > Just did a test:
> >
> > To count 132 records:
> >
> >         COUNT(*) took: 9 milliseconds.
> >         SELECT with a while loop and counter increment took 29
> milliseconds.
> >
> > Ran the test a number of times and it averaged at the same time.
> 
> would be interresting to do the same test with number of
> records like 10000, 100000 ...
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail java-thread1477@lists.mysql.com
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail java-unsubscribe@lists.mysql.com instead.
> 
> 
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail java-thread1478@lists.mysql.com
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail java-unsubscribe@lists.mysql.com instead.
> 
> 

---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail java-thread1480@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail java-unsubscribe@lists.mysql.com instead.

[prev in list] [next in list] [prev in thread] [next in thread]