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:

$row=mysql_fetch_row($result);
 if(row[0]==0) ...

Performance is becoming an issue as the table grows larger.
Is there a faster way to do this?
pillmillAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jericotolentinoCommented:
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.
PragmatiCoderCommented:
Try mysql_num_rows($result);

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

Open in new window

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:
SELECT TABLE_ROWS
FROM TABLES
WHERE TABLE_NAME = `my_table` and TABLE_SCHEMA=`my_database`
;

Open in new window

Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

Paolo SantiangeliConsulente InformaticoCommented:
Tomas Helgi JohannssonCommented:
          Hi!

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

Regards,
    Tomas Helgi
DubouxCommented:
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

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.
http://lmgtfy.com?q=Should+I+Normalize+my+Data+Base

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.