MariaDB – Handling Warnings for Empty User Defined Variables

mariadbMySQLoptimizationquery

I have 2 queries: the first query result will be passed to the second query using a user-defined variable. (This implementation is because of a performance issue with the table after applying proper indexing on large scale database)

The queries look like this:

SET @some_var=''; 
SELECT sids INTO @some_var 
  FROM fsettings 
 WHERE cid=1 
   AND tid=9 
   AND tp=1 
   AND IsDelete=0; 

SELECT name 
  FROM `tlivewsts` 
 WHERE id IN( @some_var ) 
 ORDER BY FIELD( id, @some_var );

I get a result within 1-2 sec. (before it was 30 sec.)

However, my issue is what happens if the first query returns no result (empty variable). Then, the second query throws awarning in mysql.

My question is: Is there any possible way to check whether the user-defined variable contains no result, and then directly return "0 results found"? I know this can be done through function/sp in MariaDB; I want to know if that is possible using a simple query?

I have gone through MySQL user-defined variable in WHERE clause for reference, without finding a solution.

Best Answer

After bit of R&D found below answer AND FIND_IN_SET works fine.

SET @some_var=''; 
SET @some_var:=(SELECT sids 
  FROM fsettings 
 WHERE cid=1 
   AND tid=9 
   AND tp=1 
   AND IsDelete=0); 

SELECT name 
  FROM `tlivewsts` 
 WHERE FIND_IN_SET(id ,@some_var )
 ORDER BY FIND_IN_SET(id ,@some_var );

I hope this solution is useful to some needy like me :)

Related Question