What kind of speed might I expect with request a count of selected items?
I realize my question is vague because the machine specs matter. Let's say it's a reasonably fast Mac OS X or Linux machine with 3 GB of RAM and a 5200 rpm HD. A typical machine.
Just for an example, suppose there are 200,000 user records in the users table. Each record has an integer column called "counter." I want to know how many user records have a counter greater than zero.
I still am studying SQL so my request syntax might not be right, but I think the request is something like:
SELECT COUNT(user) FROM users WHERE counter > 0;
In this case the COUNT parameter itself probably doesn't matter as long as it is some column in users, right?
Anyway, the reason I'm asking is because while I'm posting this I have a while loop executing in a different, non-SQL database. It's counting this one-by-one for all users and it looks like it will take maybe 3 hours to finish.
With a MySQL request such as this, approximately how long would I have to wait? I don't need exact numbers, just orders of magnitude. Like would you expect such a request to take seconds, tens of seconds, minutes, hours?
Also, what if the "counter" property was in a separate counters table and I did the same kind of request with an INNER JOIN like:
SELECT COUNT(user) FROM users INNER JOIN counters ON id = counters.userid WHERE counter > 0;
Would that take much longer than if the request was in one table?
Thanks (and sorry for incorrect syntax, but I think you know what I mean).