single query for multiple sql queries

Question

I am working on a project where a user can add comments and also hit any post.

Now I have to display the total number of comments and total number of hits and also show whether the user has already hitted that post or not.

So basically I need to do three sql queries for this action:

one counting comments,
one for counting hits and
one for checking whether the user has hitted the post or not.

I wanted to know that if it's possible to reduce these three sql queries into one and reduce the database load?

Any help is appreciated.

$checkifrated=mysql_query("select id from fk_views where (onid='$postid' and hit='hit' and email='$email')");//counting hits
        $checkiffollowing=mysql_query("select id from fk_views where (onid='$postid' and hit='hit' and email='$email')");
 $hitcheck=mysql_num_rows($checkifrated);//checking if already hited or not

$checkifrated=mysql_query("select id from fk_views where (onid='$postid' and comment !='' and email='$email')");//counting comments

You are likely to get more help if you supply create table statements, insert statements with relevant sample data that illustrates the problem, and what you have achieved so far. Sqlfiddle might be a good choice for the first things — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented Aug 6, 2014 at 16:13
You might get cleaner code if you use a view that defines the values you care for. Then you only have to make a single select query on that view. — arkascha
– arkascha, Commented Aug 6, 2014 at 16:14
It's still unclear - for example, you have $checkifrated and $checkiffollowing queries are exactly the same. So let's get a bit more business logic here - onid is the post, hit may or may not be 'hit', and if it is, the hit applies to the specific user noted by email, correct? Are you needing to count the number of emails where hit='hit' as well? — Jaaz Cole
– Jaaz Cole, Commented Aug 6, 2014 at 16:28

Imre L · Accepted Answer · 2014-08-06 16:52:03Z

2

This query returns the number of hits and number of nonempty comments.

select ifnull(sum(hit='hit'),0) as hits, ifnull(sum(comment !=''),0) as comments 
  from fk_views where onid='$postid' and email='$email'

Based on the queries you provided I dont think you need to query separately if he is hitted the post, just check in you code if number of hits is > 0

edited Aug 6, 2014 at 16:52

answered Aug 6, 2014 at 16:33

Imre L

6,29126 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Imre L Over a year ago

$data = mysql_fetch_assoc($result); echo $data['hits'] . ' ' . $data['comments'];

user3599194 Over a year ago

thankyou sir your answer was very helpful one last thing i want to know that is this possible that if the query returns nothing it should display 0 instead of nothing

spencer7593 · Accepted Answer · 2014-08-06 16:41:44Z

Yes, it may be possible to combine the three queries into a single query. That may (or may not) "reduce the database load". The key here is going to be an efficient execution plan, which is going to primarily depend on the availability of suitable indexes.

Combining three inefficient queries into one isn't going to magically make the query more efficient. The key is getting each of the queries to be as efficient as they can be.

If each of the queries is processing rows from the same table, then it may be possible to have a single SELECT statement process the entire set, to obtain the specified result. But if each of the the queries is referencing a different table, then it's likely the most efficient would be to combine them with a UNION ALL set operator.

Absent the schema definition, the queries that you are currently using, and the EXPLAIN output of each query, it's not practical to attempt to provide you with usable advice.

UPDATE

Based on the update to the question, providing sample queries... we note that two of the queries appear to be identical.

It would be much more efficient to have a query return a COUNT() aggregate, than pulling back all of the individual rows to the client and counting them on the client, e.g.

SELECT COUNT(1) AS count_hits
  FROM fk_views v
 WHERE v.onid  = '42'
   AND v.hit   = 'hit'
   AND v.email = '[email protected]'

To combine processing of the three queries, we can use conditional expressions in the SELECT list. For example, we could use the equality predicates on the onid and email columms in the WHERE clause, and do the check of the hit column with an expression... For example:

SELECT SUM(IF(v.hit='hit',1,0)) AS count_hits 
     , SUM(1)                   AS count_all
  FROM fk_views v
 WHERE v.onid = '42'
   AND v.email='[email protected]'

The "trick" to getting three separate queries combined would be to use a common set of equality predicates (the parts of the WHERE clause that match in all three queries).

SELECT SUM(IF(v.hit='hit'  ,1,0)) AS count_hits 
     , SUM(IF(v.comment!='',1,0)) AS count_comments
     , SUM(1)                     AS count_all
  FROM fk_views v
 WHERE v.onid = '42'
   AND v.email ='[email protected]'

If we are going to insist on using the deprecated mysql interface (over PDO or mysqli) it's important that we use the mysql_real_escape_string function to avoid SQL Injection vulnerabilities

$sql = "SELECT SUM(IF(v.hit='hit'  ,1,0)) AS count_hits
             , SUM(IF(v.comment!='',1,0)) AS count_comments
             , SUM(1)                     AS count_all
          FROM fk_views v
         WHERE v.onid = '" . mysql_real_escape_string($postid) . "'
          AND v.email = '" . mysql_real_escape_string($email) ;

# for debugging
#echo $sql

$result=mysql_query($sql);
if (!$result) die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    echo $row['count_hits'];
    echo $row['count_comments'];
}

For performance, we'd likely want an index with leading columns of onid and email, e.g.

... ON fk_views (onid,email)

The output from EXPLAIN will show the execution plan.

Collectives™ on Stack Overflow

single query for multiple sql queries

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related