CODEWITHSUNDEEP
phpmysql
ShoeLace1291
ShoeLace1291

Reputation: 4698

Is it faster to use one large query or several smaller queries?

I've been pondering this for some time and thought I would ask some people that are knowledgeable of MySQL and even PHP. Let's say you have a query that gets dynamic information about an article. You also have several tables associated with the articles such as comments and categories. Let's say that on a page of article results you would like to get information such as the number of comments and what category the article is in. Which method would load faster?

One Large Query: 

$query = "
     SELECT 
          a.article_id, a.title, a.description, a.category_id
          com.comment_id, com.article_id, COUNT(com.article_id) AS num_comments
          cat.category_id, cat.title AS cat_titleca
     FROM articles AS A
     LEFT JOIN categories AS cat ON (a.category_id = cat.category_id)
     LEFT JOIN comments AS com ON (a.article_id = com.comment_id)
     GROUP BY a.article_id
     ORDER BY a.article_id DESC
     LIMIT 10";

if($query = mysql_query($query)){

     if(mysql_num_rows($query) > 0){

          while($article=mysql_fetch_assoc($query)){

              echo $article['title'].' has '.$article['num_comments'].' comments and is in the category '.$article['cat_title'].'.';

          }

     }

}

Or to use several smaller queries:

<?php

$query_articles = "
     SELECT
          article_id, title, description, category_id
     FROM articles
     ORDER BY article_id DESC
     LIMIT 10";

if($query_articles = mysql_query($query_articles)){

     if(mysql_num_rows($query_articles) > 0){

          while($article=mysql_fetch_assoc($query_articles)){

               $query_comments = "
                    SELECT
                         comment_id, article_id
                    FROM comments
                    WHERE article_id = ".$article['article_id']."
                    ORDER BY comment_id";

               if($query_comments = mysql_query($query_comments)){

                    $num_comments = mysql_num_rows($query_comments);

               }

               $query_cat = "
                    SELECT
                         category_id, title
                    FROM categories
                    WHERE category_id = ".$article['category_id']."
                    LIMIT 1";

               if($query_cat = mysql_query($query_cat)){

                    if(mysql_num_rows($query_cat) > 0){

                         $cat = mysql_fetch_assoc($query_cat);

                    }

                }

                echo $article['title'].' has '.$num_comments.' and is in the category '.$cat['title'].'.';

          }

     }

}

Upvotes: 0

Views: 52

Answers (1)

meda
meda

Reputation: 45500

A single query is faster of course, why don't you do benchmark

$time = microtime(true);
//code here
$elapsed = microtime(true) - $time;
echo 'Elapsed time = '.$elapsed;

Upvotes: 4

Related Questions