Fastest SQL to determine if query returns data?

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?
Who is Participating?
Ray PaseurCommented:
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
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.
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nemws1Database AdministratorCommented:
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

Paolo SantiangeliConsulente InformaticoCommented:
Tomas Helgi JohannssonCommented:

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.