Fastest SQL to determine if query returns data?

Posted on 2011-10-04
Last Modified: 2012-05-12
What is the fastest way to determine if a query has not returned any data?

I am currently using im MySQL: SELECT count(var) from table  ...;

The result is tested (in php)  for a zero result:

 if(row[0]==0) ...

Performance is becoming an issue as the table grows larger.
Is there a faster way to do this?
Question by:pillmill
    LVL 5

    Assisted Solution

    I still use SELECT COUNT(*) AS `count` FROM `table`. MyISAM maintains a cache and I believe InnoDB counts them every time, but still makes use of the indexes. I had a similar issue where I had to count millions of rows and just opted for an AJAX call that let the page load and then loaded the number of rows after MySQL had finished counting.
    LVL 5

    Expert Comment

    Try mysql_num_rows($result);

    $query = "SELECT * FROM table WHERE 1";
    $result = mysql_query($query);
        //do something
    } else {
       //query returned zero records

    Open in new window

    LVL 23

    Expert Comment

    As jericotolentino mentions, it should be fast.  Make sure you have a PRIMARY KEY or a UNIQUE index specified on the table.

    You can also make use of the "information_schema" database (if you have access to it), although this might take the same amount of time on an un-indexed table:
    WHERE TABLE_NAME = `my_table` and TABLE_SCHEMA=`my_database`

    Open in new window

    LVL 11

    Expert Comment

    LVL 24

    Expert Comment

    by:Tomas Helgi Johannsson

    If you have a table with column which has the same cardinality as the table itself (a.k.a column in primary key or unique key) you
    could do

    select count(column) from table

    instead of

    select count(*) from table

    which gives a databases ability or forces it to
    do a count on an index instead of the table itself which is a lot faster. :)

        Tomas Helgi
    LVL 3

    Expert Comment

    I can't see why you would want to know if a query would return records, if you wouldn't want to show those records afterwards...
    If this is the case and you use count(..) then you'd need a 2nd query to load the data..
    which is double work.

    You could run a query fetching the data you'd need later on, and have php check if there's any records at all, using what pragmaticoder said:

    $Sql = "SELECT .. FROM ... WHERE ...";
    $Result = mysql_query($Sql) or ....;
    $NumRows = mysql_num_rows($Result);
    echo "I found ".$NumRows." records.";
    if ($NumRows == 0) {
    echo "no results";
    else {
    while ($row = mysql_fetch_assoc($Result)) {
    // do what u need...

    Open in new window

    LVL 107

    Accepted Solution

    If performance is becoming an issue as the table grows larger, it is time to learn about normalization.  The results of this search are particularly interesting, giving points on each side of the issue.

    The methods of getting a count will not matter to performance.  But there are many things that do matter to performance.

    The first thing I would do would be to use ALTER TABLE and add indexes on every column that is used in WHERE, ORDER, GROUP or JOIN clauses.  The next thing I would do is use EXPLAIN SELECT on all complex queries to see how the MySQL engine is handling the query.  The third thing would be a bit more work, but it's sometimes necessary since programmers often write SELECT * without understanding the ramifications.  That would be to go into the code and refactor any SELECT * queries to select only the required columns.  After that I would look for any queries (including UPDATE) that do not have LIMIT clauses, but could have LIMIT clauses.  One example that often turns up is SELECT * FROM myTable WHERE key = $_GET["key"].  If the key in this example is unique, the query should have LIMIT 1 to prevent a table scan.

    HTH, ~Ray

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now