0

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.

4 Answers 4

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

0

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

6
  • sids contains comma separated ids like "1,2,3,4,5,6" ,However it is working fine but if I want to go with UDV then is it possible? because this is just one example and I want to know for my knowledge.
    – Smit Shah
    Jan 24, 2019 at 13:58
  • @SmitShah JOINs are intended to connect tables. You can do that on your own by UDVs but you'll never get the same efficiency and performance.
    – Kondybas
    Jan 24, 2019 at 14:09
  • Yes you are absolutely right but I am trying with UDV because in real case each of parent query is depends upon parent query (sub-queries) and each query is written in php , If i go with joins then there will be lots of changes in code for developers and if I got the solution for UDV then there will be minimal changes in code and In terms of mysql connection there will be only 1 connection will be made this will also increases query performance just like join but innodb buffer usage will be same as join query(I think)
    – Smit Shah
    Jan 25, 2019 at 4:04
  • 1
    @SmitShah - You are talking as if you are the DBA, and the programmers are on the other side of a difficult-to-breech wall. Then consider the following: Make a Stored Procedure that encapsulates even more of the processing that what you show. This might appease them.
    – Rick James
    Jan 25, 2019 at 6:27
  • @RickJames I beleive that every database should be wrapped into the API-like set of stored routines and shouldn't be accessible directly by anyone except the DBA.
    – Kondybas
    Jan 25, 2019 at 9:29
0

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

1
  • I know and this question is raise just because of that and I am searching solution to avoid this warning, if possible then we can eliminate this error by if else condition by checking value of UDV ( or any other way to check) , if value is exist then only fire second query.(just for an example).
    – Smit Shah
    Jan 25, 2019 at 4:10
0

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.

2
  • Thanks for answer and suggesting way for how to implement this query but I have also think about UDV as it require minimal code changes so.
    – Smit Shah
    Jan 25, 2019 at 4:07
  • @SmitShah - What you have simply cannot be made to work without code changes.
    – Rick James
    Jan 25, 2019 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.