mysql_num_rows

[LanceT]

If I have a huge table with 100000 rows, how intensive is it to use

 

mysql_num_rows to get a number of rows?

 

If it is intensive, what alternatives are there to get a number of rows?

[artacus]

SELECT COUNT(*) FROM hugeTable

[LanceT]

SELECT COUNT(*) FROM hugeTable

 

you didnt really answer my question. Is mysql_num_rows inefficient? Is SELECT COUNT better?

[LanceT]

Heres some of the code Im using

 

$get = mysql_query("SELECT * FROM getem WHERE get1='$user' OR get2='$user'", $dbh); 

$getnum = mysql_num_rows($get);

 

Assuming that the table getem is HUGE, would this query take up a lot of resources?

[artacus]

how intensive is it to use

mysql_num_rows to get a number of rows?

 

I assumed you meant "get THE number of rows", in which case it is VERY inefficient. It has to select every row from the table and return it. So in that situation COUNT(*) would be a huge performance gain.

 

Now the second time around, you show me a query that probably returns one or two rows. In that case it really wont make much of a difference, especially since you are probably going to want to know who they are.

[LanceT]

how intensive is it to use

mysql_num_rows to get a number of rows?

 

I assumed you meant "get THE number of rows", in which case it is VERY inefficient. It has to select every row from the table and return it. So in that situation COUNT(*) would be a huge performance gain.

 

Now the second time around, you show me a query that probably returns one or two rows. In that case it really wont make much of a difference, especially since you are probably going to want to know who they are.

 

so my query, even if run on a 1,000,000 row table, won't take up too many resources?

 

Thanks for the input.

[artacus]

Well the tricky part is finding which records match. Its no big deal IF you've got get1 and get2 indexed. Whether you are returning the results or just counting them doesn't really matter.

[LanceT]

Well the tricky part is finding which records match. Its no big deal IF you've got get1 and get2 indexed. Whether you are returning the results or just counting them doesn't really matter.

 

alright well I indexed them, but also is there any loss if I index rows? If not, then why don't people just index everything?

[artacus]

You don't index everything because it slows write performance a bit (it not only has to write the data but update the index as well) and it takes up more disk space.

 

We just had a discussion earlier this week on what to index.

[LanceT]

o ok, where is that discussion located?

[fenway]

Basically, it comes down to having to retrieve the contents of each of the matching records, versus just counting if any records are found -- the latter is almost always faster, unless you're going to examine each one in turn anyway.

[artacus]

http://www.phpfreaks.com/forums/index.php/topic,130109.0.html