[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Fastest SQL to determine if query returns data?

Posted on 2011-10-04
Medium Priority
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

Assisted Solution

jericotolentino earned 1000 total points
ID: 36913963
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.

Expert Comment

ID: 36913974
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

ID: 36913978
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 11

Expert Comment

by:Paolo Santiangeli
ID: 36913980
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 36915642

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

Expert Comment

ID: 36916171
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 111

Accepted Solution

Ray Paseur earned 1000 total points
ID: 36918327
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 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