Jump to content


Photo

Fastest COUNT plus data method?


  • Please log in to reply
15 replies to this topic

#1 joeygallo

joeygallo

    Member

  • Members
  • PipPip
  • 13 posts

Posted 14 May 2013 - 09:16 PM

I need to COUNT then get data if rows exist.  Something like this:

$something = mysql_query("select COUNT(*) from table");
if (mysql_result($something,0) > 0) {
$qry = mysql_query("select field1, field2, field3 from table");
$result = mysql_fetch_array($qry);
echo $qry['field1'];
// etc. 
} else {
echo "no data found";
}

 Is the above the best way to do this or can the two queries be combined?  I'm looking for the fastest possible method as we'll be dealing with a table that will eventually have 50 million+ rows.

 

Thank you.


Edited by joeygallo, 14 May 2013 - 09:16 PM.


#2 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,904 posts
  • LocationWA

Posted 14 May 2013 - 09:27 PM

If it'll have that much data then you don't need to bother with a count, right?

#3 joeygallo

joeygallo

    Member

  • Members
  • PipPip
  • 13 posts

Posted 15 May 2013 - 01:47 AM

If it'll have that much data then you don't need to bother with a count, right?

 

Not exactly.  We still need to display the count number.  We found X number of results.. Using mysql_num_rows is not an option with that many records.



#4 joeygallo

joeygallo

    Member

  • Members
  • PipPip
  • 13 posts

Posted 15 May 2013 - 01:49 AM

Not exactly.  We still need to display the count number.  We found X number of results.. Using mysql_num_rows is not an option with that many records.

 

Also, the better answer would be select COUNT(*) from table where field1 = 'xyz'.  There we have 50 million rows in the table but maybe not for the specific search.  This is the primary reason COUNT is still needed.



#5 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,904 posts
  • LocationWA

Posted 15 May 2013 - 02:17 AM

With that WHERE the question makes more sense.

If you need accuracy then a SELECT COUNT(*) will do it, but make sure you have indexes on the fields you're using. If you don't need an exact figure an EXPLAIN SELECT may give you numbers close enough.

#6 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 15 May 2013 - 03:24 AM

You could always use the "num_rows" property/function of the SQL library you're using.
For MySQLI it's mysqli_result::$num_rows and for PDO it's PDOStatement::rowCount. Unfortuantely PDO doesn't guarantee it's available for all RDBM systems, but it should be available for MySQL.
Keeping it simple.

#7 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,904 posts
  • LocationWA

Posted 15 May 2013 - 12:24 PM

You could always use the "num_rows" property/function of the SQL library you're using.
For MySQLI it's mysqli_result::$num_rows and for PDO it's PDOStatement::rowCount. Unfortuantely PDO doesn't guarantee it's available for all RDBM systems, but it should be available for MySQL.

...Which would require querying for and downloading all howevermanymillion rows. No thanks.

#8 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,129 posts
  • LocationCheshire, UK

Posted 15 May 2013 - 01:19 PM

I need to COUNT then get data if rows exist.  Something like this:

From the opening post it sounds like he's going to get them anyway once he's counted how many there are


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#9 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,904 posts
  • LocationWA

Posted 15 May 2013 - 01:56 PM

From the opening post it sounds like he's going to get them anyway once he's counted how many there are

He said the table will have millions of rows. Unless there's going to be a very specific WHERE, there'll be some amount of pagination.

#10 joeygallo

joeygallo

    Member

  • Members
  • PipPip
  • 13 posts

Posted 16 May 2013 - 06:42 PM

Sorry for the long reply. There is a "WHERE".

 

select COUNT (*) from ip_logs where ip = '123.123.123.123'

 

IF FOUND:

 

select field1, field2, field3 from ip_logs where ip = '123.123.123.123'

 

In this query, I just need to know if it exists, not an actual count.  If so, move on to the next query to get data from that row.  So my question is.. what is the fastest possible method?  I assume the two can be combined but my concern is speed.



#11 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,129 posts
  • LocationCheshire, UK

Posted 16 May 2013 - 07:04 PM

A )

SELECT COUNT(*) WHERE ...
if found
SELECT blah

B )

SELECT blah
get num_rows

Try both and benchmark them, then you'll know. (I'm backing B )


Edited by Barand, 16 May 2013 - 07:05 PM.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#12 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,904 posts
  • LocationWA

Posted 16 May 2013 - 07:40 PM

If you just want to know if there's matching rows you can also
SELECT 1 FROM table WHERE conditions LIMIT 1
If you get back anything at all then you know there's at least one.

#13 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,129 posts
  • LocationCheshire, UK

Posted 16 May 2013 - 08:11 PM

I did my own benchmarking on a 200K record table

$t1 = microtime(1);

$sql = "SELECT COUNT(*) FROM votes WHERE type=2";
$res = $db->query($sql);
$row = $res->fetch_row();
$count = $row[0];
if ($count > 0) {
    $res = $db->query("SELECT * FROM votes WHERE type=2");
}

$t2 = microtime(1);

$sql = "SELECT * FROM votes WHERE type=2";
$res = $db->query($sql);
$count = $res->num_rows;

$t3 = microtime(1);

printf("Rows found : %d<br>Method A : %0.4f<br>Method B : %0.4f", $count, $t2-$t1, $t3-$t2);

/******* RESULTS *****************************
Rows found : 0
Method A : 0.0009
Method B : 0.0008

Rows found : 9278
Method A : 0.0582
Method B : 0.0246 
**********************************************/

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#14 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,972 posts
  • LocationSpring, TX USA

Posted 16 May 2013 - 08:56 PM

select COUNT (*) from ip_logs where ip = '123.123.123.123'


If you are actually going to be storing millions of IP Addresses, you may want to look at mySql's INET_ATON() and INET_NTOA() functions. These convert the dotted-decimal IPv4 address to a single 4-byte unsigned integer. Instead of storing 7 to 17 characters for each address, you store 4 bytes each. Your indexes will also be smaller.
-- I haven't lost my mind, it's backed up on tape ... somewhere!

#15 joeygallo

joeygallo

    Member

  • Members
  • PipPip
  • 13 posts

Posted 16 May 2013 - 10:25 PM

 

I did my own benchmarking on a 200K record table

$t1 = microtime(1);

$sql = "SELECT COUNT(*) FROM votes WHERE type=2";
$res = $db->query($sql);
$row = $res->fetch_row();
$count = $row[0];
if ($count > 0) {
    $res = $db->query("SELECT * FROM votes WHERE type=2");
}

$t2 = microtime(1);

$sql = "SELECT * FROM votes WHERE type=2";
$res = $db->query($sql);
$count = $res->num_rows;

$t3 = microtime(1);

printf("Rows found : %d<br>Method A : %0.4f<br>Method B : %0.4f", $count, $t2-$t1, $t3-$t2);

/******* RESULTS *****************************
Rows found : 0
Method A : 0.0009
Method B : 0.0008

Rows found : 9278
Method A : 0.0582
Method B : 0.0246 
**********************************************/

Thanks.  Method A is what we have now.  I think it sounds like we just need some indexing done to the database.  50 million rows is basically a timeout.  Indexing will likely fix it.



#16 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,129 posts
  • LocationCheshire, UK

Posted 17 May 2013 - 04:11 AM

The results were pretty much what I expected. (Times above in seconds)

 

Not much in it if no records are found but, once you find records, method B is 2 times faster than A.

 

Method A reads the records twice (count them, fetch them) whereas B counts them while it is fetching them.


Edited by Barand, 17 May 2013 - 04:12 AM.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com