SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Tokyo
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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).

    doug

  2. #2
    SitePoint Member cms_geek's Avatar
    Join Date
    Dec 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, just to clarify - is the counter column you're referring to a field, or are you trying to count individual posts associated with a user?

    It seems to me that you're counting for every row in a column with value > 0 and I can see why that can take long for 200,000 (x 200,000 = 40,000,000,000?!). If it is a physical value that you are after you should remove the 'COUNT' function.

    I may be shooting from my hip, but I'm a little uncertain.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Tokyo
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The counter column is a field. I'm trying to count individual "somethings" with a user, not necessarily posts.

    The loop thing over 200,000 users doesn't have anything to do with a current MySQL implementation. I'm sorry if I confused the discussion with that. The loop is taking place in completely separate kind of database with no query language so I am forced to write a script to manually inspect the contents of the database and check each counter value one-by-one to see if it is greater than zero. It really is looping just 200,000 times though - and incrementing a variable each time it finds a counter value greater than zero. Then it just reports how many it found at the end.

    At the same time, I also happen to be trying to learn PHP and MySQL so I was curious if this kind of query was faster in MySQL.

    What I want to know is "how many users have a 'counter' field value that is greater than 0".

    Thanks,

    doug

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your query is fine, except you'll want COUNT(*) instead of COUNT(user)

    and it should complete in seconds
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Tokyo
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That kind of speed really is amazing considering some of what I've been going through with the database I currently use.

    A followup question!

    Why would I want to Count(*) rather than COUNT(user)? Why, in fact, does it matter what I count from the table so long as I count something that exists in every record?

    Thanks,

    doug

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    counting column values that may or may not exist is slower because each value needs to be inspected to see if it's NULL

    COUNT(*), however, doesn't do that, it just counts rows

    for MyISAM tables, it's even faster, as it doesn't even have to count -- the number of rows is a separately-maintained item of information that it can just retrieve

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Tokyo
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah! I didn't realize that basic point. I thought Count(*) needed to retrieve all the fields for some reason and was therefore slower. I was thinking backwards.

    Thanks.

    doug


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •