pillmill
asked on
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($resu
if(row[0]==0) ...
Performance is becoming an issue as the table grows larger.
Is there a faster way to do this?
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($resu
if(row[0]==0) ...
Performance is becoming an issue as the table grows larger.
Is there a faster way to do this?
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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:
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`
;
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
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
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:
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...
}
}
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
PHP
PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.
125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Eg.
Open in new window