CODEWITHSUNDEEP
mysqlsqlprocedure
3Nex
3Nex

Reputation: 527

MySQL: How come the procedure is slower than executing the query?

So i have this superduper query that finds "related articles" based on the number of tags they have in common with the original article (provided in the $id variable). I don't know if the actual query is important, but here it is Justin Case.

Now, I've never actually used procedures in a live project, but I've read they should be faster, partially because the MySQL engine doesn't need to interpret the code each time. But when i had put this same code in a procedure and called the procedure, the execution was on average about 450 times longer.

Why? Is it because it's returning multiple rows? Do procedures stink at that? Is it because i have to use an input variable in my procedure? 450 is a bunch! 

SELECT a.id, a.image, a.title, a.excerpt, a.permalink, COUNT(rel.category_id) AS n
  FROM articles AS a
  JOIN category_relations AS rel ON rel.article_id = a.id
  JOIN categories AS c ON rel.category_id = c.id
 WHERE rel.category_id IN (SELECT category_id 
                             FROM category_relations 
                            WHERE article_id = {$id})
   AND a.id != {$id}
   AND c.type = 1
GROUP BY rel.article_id
ORDER BY n DESC, publish_date DESC
   LIMIT 10

Code used to create the procedure:

DROP PROCEDURE IF EXISTS get_related_articles;
DELIMITER //
CREATE PROCEDURE get_related_articles(IN id INT)
BEGIN
   SELECT   a.id, a.image, a.title, a.excerpt, a.permalink, COUNT(rel.category_id) AS n
   FROM     articles AS a
    JOIN  category_relations AS rel ON rel.article_id = a.id
    JOIN  categories AS c ON rel.category_id = c.id
   WHERE    rel.category_id IN ( SELECT category_id FROM category_relations WHERE article_id = id)
    AND   a.id != id
    AND   c.type = 1
   GROUP BY rel.article_id
   ORDER BY n DESC, publish_date DESC
   LIMIT    10;
END //
DELIMITER ;

Upvotes: 0

Views: 1203

Answers (2)

ravnur
ravnur

Reputation: 2852

Welcome to the real world of MySQL! Sometimes it's very hard to say why one query executes longer that another. But in your case the answer can be find here:

MySQL doesn't use cache for queries have been called from stored procedures.

Upvotes: 1

DRapp
DRapp

Reputation: 48139

Not positive on your delay, but IN SUBSELECTS can themselves be costly. Have you thought of doing a join to what is now your sub-select? Also, since the articles table is basis of query, and a.id = rel.article_id, your group by on the "a.id" might be better if available via index.

SELECT 
      a.id, 
      a.image, 
      a.title, 
      a.excerpt, 
      a.permalink
   FROM 
      articles AS a
         JOIN category_relations AS rel 
            ON a.id = rel.article_id 
            JOIN categories AS c 
               ON rel.category_id = c.id
               AND c.type = 1
            JOIN (SELECT category_id 
                     FROM admin_category_relations 
                     WHERE article_id = {$id} ) RelByArticle
               on rel.category_id = RelByArticle.category_id
   WHERE 
      a.id != {$id}
   GROUP BY 
      a.id
   ORDER BY 
      n DESC, 
      publish_date DESC
   LIMIT 10

Upvotes: 0

Related Questions