MariaDB User Define Variable throws warning if it is empty

Question

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.

Answer 1

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 :)

Answer 2

You can completely avoid that trick with UDV by using JOIN

SELECT b.name
  FROM fsettings AS a
  JOIN tlivewsts AS b  ON b.id = a.sids -- here is the replacement for UDV
 WHERE a.cid=1 
   AND a.tid=9 
   AND a.tp=1 
   AND a.IsDelete=0; 
 ORDER BY b.id;

Sure for acceptable performance your table fsettings should be properly indexed. You need something like (sids, cid, tid, tp, IsDelete) (the order of fields may vary).

Answer 3

The first SELECT should give a warning:

SHOW WARNINGS;

| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |

So it should be possible to check for warnings in your program code, and then only execute the second query if there are no warnings. (How to check for warnings depends on the programming language and library/connector.)

Answer 4

I assume sids is a commalist of numbers?? Like "11,33,654"??

Then look at what your query becomes:

SELECT name 
  FROM `tlivewsts` 
 WHERE id IN( "11,33,654" ) 
 ORDER BY FIELD( id, "11,33,654" );

That works for FIELD(), but not for IN, which needs a commalist of numbers, not a string:

 WHERE id IN(  11,33,654  ) 

Plan A: Do not use commalists. Instead build another table with the items in question.

Plan B: Write a Stored Procedure that builds the SELECT via CONCAT(), then PREPAREs it and EXECUTEs it.