1

I have a MySQL database that contains over 400,000 rows. For my web based script, I have a page function. One of the steps to determine how many pages there should be is returning the number of rows in the table.

Let's pretend the table name is data.

I'm wondering what is the most efficient method to ONLY return the number of rows in the database.

I could obviously do something like:

$getRows = mysql_query("SELECT id FROM `data`") or die(mysql_error());
$rows = mysql_num_rows($getRows);

So that it only selects the id. But still, that will be selecting 400,000 + ID's worth of data and storing it on the stack (i think?) and seems less efficient as using a method such as finding the table status. I'm just not 100% sure how to use the table status method.

Feedback & opinions would be awesome. Thanks guys!

Chris Ingis
  • 696
  • 5
  • 8
  • 1
    @itachi SO TRUE!..and btw mysql_ is being depreciated......red box.....pdo.....guide.... – d-_-b Oct 10 '12 at 16:15
  • Will PHP be replacing it with a new query method? – Chris Ingis Oct 10 '12 at 16:17
  • yes please google `mysql` or `mysqli` or `mysql pdo` to read about the change. or search Stackoverflow for `mysql` and usually the first comment on the question references the improper use of `mysql_` – d-_-b Oct 10 '12 at 16:21

2 Answers2

4

use count

SELECT count(id) FROM data

See this question for more info on getting counts. Make sure your id has an index in your table.

Now, to find the number of unique rows, you can do

SELECT count(distinct(id)) FROM data

alternatively, if you want to find the highest ID number (if you ID are autoincremental and unique) you can try SELECT max(id) FROM data to return the highest ID number present.

I'd highly recommend this site to learn these basic functions: http://sqlzoo.net/
Community
  • 1
  • 1
d-_-b
  • 21,536
  • 40
  • 150
  • 256
2

400,000 rows is not a lot at all. Keep it simple and just do:

select count(*) 
from `data`
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • It is if I'm displaying 15 results per page because i have a "jump to page" function that lists all the pages (Over 26666). It's heavy on the HTML drawing. – Chris Ingis Oct 10 '12 at 16:16
  • it is 2666 or 266666666666 doesn't matter. If you want to reduce the number, then make that 15 into 50 or 100. Pass the page number as get variable and retrieve the data. – itachi Oct 10 '12 at 16:26
  • What I mean is, on the web side, it will take HTML longer to draw 26666666 option tags than 2666. Thanks for your help. – Chris Ingis Oct 10 '12 at 16:36
  • I have no idea why you are talking about HTML, I thought this was a SQL question... – D'Arcy Rittich Oct 10 '12 at 16:45
  • Why you are outputting all the numbers? Put only the relevant ones. – itachi Oct 10 '12 at 16:48