MYSQL Best Row Count Method - Large Database

Question

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!

@itachi SO TRUE!..and btw mysql_ is being depreciated......red box.....pdo.....guide.... — d-_-b
– d-_-b, Commented Oct 10, 2012 at 16:15
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
– d-_-b, Commented Oct 10, 2012 at 16:21

Community · Accepted Answer · 2017-05-23 11:58:09Z

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/

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Oct 10, 2012 at 16:14

d-_-b

23.2k43 gold badges174 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

itachi Over a year ago

alternate way has flaws though. what if some datas get deleted? though upvoted for the 1st part.

Chris Ingis Over a year ago

Yeah the data is not reliable enough to count by highest ID. There would be "gaps." Thanks

D'Arcy Rittich · Accepted Answer · 2012-10-10 16:13:15Z

2

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

select count(*) 
from `data`

answered Oct 10, 2012 at 16:13

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

5 Comments

Chris Ingis Over a year ago

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.

itachi Over a year ago

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.

Chris Ingis Over a year ago

What I mean is, on the web side, it will take HTML longer to draw 26666666 option tags than 2666. Thanks for your help.

D'Arcy Rittich Over a year ago

I have no idea why you are talking about HTML, I thought this was a SQL question...

itachi Over a year ago

Why you are outputting all the numbers? Put only the relevant ones.

Collectives™ on Stack Overflow

MYSQL Best Row Count Method - Large Database

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related