Same Parameter in MySQL stored procedure

Question

How do I cache dynamic query from a stored procedure?

Right now I have created my stored procedure like this :

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  .... 
)
BEGIN

   SET @sqlQuery = CONCAT("SELECT Name From Users WHERE UserID > ", UserID, " AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ", UserID, " ) Order by Name") 

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

END;

But this kind of query does not cached. So, every time it gets more time to execute/process query.

Now I have tried some other method like this:

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  .... 
)
BEGIN
   SET @UserID = UserID;

   SET @sqlQuery = "SELECT Name From Users WHERE UserID > ? AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ? ) Order by Name";

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt @UserID, @UserID; -- here i passed same variable twice.
   DEALLOCATE PREPARE stmt;
END;

In the above case I have to pass same variable (@UserID) twice, because it is used 2 times in my query. but this job is very hectic in long or complex query. so, how do I avoid this?

One another method I tried as follows:

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  .... 
)
BEGIN
   SET @UserID = UserID;

   SET @sqlQuery = "SELECT Name From Users WHERE UserID > @UserID AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = @UserID ) Order by Name";

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
END;

But above query again does not cached. so, execution time is very long. And this type of variable declared as session global variable has may be conflict with another stored procedure's variable. Because I have call stored procedure within this stored procedure and variable name should be same in another stored procedure.

So, let me know what is the best solution for the same.

Thanks.

Answer 1

First of all, I don't think it's a good idea to use dynamic sql in the case when you have nothing really 'dynamic'.

If you still want to do it, it seems that you are missing an important point about prepared statements. You are preparing, executing, and then deallocating prepared statement in one procedure; thus, next time you call your procedure, you are repeating all the steps again. Take a look on https://stackoverflow.com/questions/11605783/mysql-permanently-prepared-statements , there is an example on how to get benefits from prepared statements inside stored procedure (briefly, you do not deallocate statement at the end, instead of this, you check whether a variable associated with the statement is not null and call prepare only once. That expands lifetime of the prepared statement to lifetime of session effectively caching execution plan).

Answer 2

Dynamic queries are all about being dynamic - so can't be cached...
What I'd do is indexing the tables based on the fields you'll use in your WHERE clause, and create Views from affected tables with ORDER BY clause (and make the dyn-query SELECT work from the Views instead of the tables of course).
I'd also recommend reading this guy here: API Prepared Statements in MySQL, as well as using the USING keyword in EXECUTE, like EXECUTE stmt USING @UserID, @UserID;

Hope this helps!