which is better?

phigga

Hi all,

I have a table with an auto incrementing primary key. I need to find the number of rows in that table. Is it better to do a

SELECT count(*)

or a

SELECT keyID FROM table ORDER BY keyID DESC LIMIT 1

and why is each better/worse than the alternative?

thanks,
-Phil

Anon

use select count(*) from table

the reason:
if you delete a record from your table, a gap will be left and therefore the result you get from the second method may be incorrect.

John
[ black dog, brown dog, you decide @ smashedbug.com ]

phigga

Good call.

thanks....

Anon

Remember, it's not nice to fool Mother SQL!

Seriously, folks, why is it that we spend so much time looking for inscruitable workarounds when the obvious method is right there in standard SQL supported by every database that even pretends to be sql-compliant? You want a count, ask for count(). What's so hard about that? :-)

phigga

-- What's so hard about that? :-)

Nothing at all...I still can't believe I posted that. It was after many hours/days/weeks/months of durress over a huge project. We had just been going over why we need transactions, why MySQL doesn't support transactions, and what we can do about it.

What can I say...I needed a count, and I (at the time) wasn't convinced that count() was the best way to do it.

must....sleep........................

Anon

hmm.. i thought mysql did support transactions :-)

John

jjoske

The lastest version is supposed to support transactions, but I've never spoken to anyone who has ever used it so I don't know what it is like.

phigga

From what I understand, MySQL's support of transactions (through BerkelyDB libraries) is in beta stages.

But i can't guarantee that...

Anon

check out innodb for better transaction support .. not only does it support row level locking, true transactions, etc..etc.. but its fast .. 🙂 check out innodb.com for more details.

john
[ black dog, brown dog - you decide @ smashedbug.com ]

phigga

i'll check that out...have you ever dealt with or heard of NuSphere? That's the solution my company is currently looking towards....any comments about that one?