SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    England
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    COUNT(*) OR SELECT to find a single result

    Generally, what is the most efficient way to get a Yes/No answer as to whether there is at least 1 result to a query:

    $db->query("SELECT COUNT(*) AS total FROM x WHERE Y");
    $result = $db->get_row();
    if ($result['total'] > 0) {
    ...
    } else {

    }

    or:

    $db->query("SELECT j AS y FROM x WHERE Y LIMIT 1");
    if ($db->num_rows()) {
    ...
    } else {

    }

    Does it make any difference? My guess is the 2nd is more efficient because it only scans one row, can a COUNT(*) be made to only scan for 1 row?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    whether there is at least one result for a query depends on the query

    if you search a million row accounting table for an out-of-balance condition, you may or may not get a result back, but you still have to scan a million rows

    whether there is at least one row in a table is a different question, and count(*) is fastest for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    England
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am talking about running it on table to find a matching row. Generally the criteria will be on a single indexed column.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if the query has a WHERE clause with a condition that involves an indexed column, and if your second query (the one using LIMIT, not count(*)) selects only that column, then i really don't know which will be faster

    test it yourself on your own data, run it both ways, compare the execution plans (EXPLAIN) and compare the elapsed and cpu time differences
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •