List:General Discussion« Previous MessageNext Message »
From:Magne Westlie Date:February 11 2008 8:44am
Subject:Re: stored procedure, parameter type help needed
View as plain text  

Jerry Schwartz wrote:
>>    SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
>> uid
>> FROM temp_uids);
> [JS] Couldn't you replace the " WHERE user_id IN (SELECT uid FROM
> temp_uids)"
> with a simple JOIN? If "IN" is badly optimized, as I've read here, wouldn't
> that be more efficient? Or am I (as usual) missing something?

I may be the one missing something :-). I have tried to think out of the 
box and use other solutions, but haven't come up with a way that works 
without using "IN".

The query are to be used in a calendar-ish application, for finding when 
people are free to attend meetings. The ids I send as parameter is the 
ids of users that I want to check availability for. The id-list may 
contain between 1 and 50 user ids.

Maybe I could use

JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...)  (?)

but as far as I understand, I'd still have to generate this list as a 
string because I do not know how many users to check for, and then 
CONCAT the query, PREPARE etc. as described in Peter Brawley's email. 
Then I think I prefere using "IN".

As for the optimization of "IN", I've read the following in the manual:

"The search for the item then is done using a binary search. This means 
IN is very quick if the IN value list consists entirely of constants."

(http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in")

, so if I am to rely on the doc, it should be "very quick" the way I use it.

Thanks for your comment Jerry.


Magne




> 
>>    DROP PREPARE stmt;
>> END; ::
>> DELIMITER ;
>>
>> CALL get_users('(2), (3)');
>> -----------------------------------------------------------
>>
>>
>> MW
>>
>>
>> Peter Brawley wrote:
>>> Hi Magne
>>>
>>>  >...the query I actually want to use this in, is a 100 line query
>> with
>>> lots of arguments.
>>>  >I don't feel to good about creating it into a bunch of strings (16)
>>> that I have to
>>>  >concatenate with the variables inbetween.
>>>
>>> The only alternative I know for current versions of MySQL is to
>> assemble
>>> the query in the app layer.
>>>
>>>  >Also, I was moving the query into a stored procedure because I
>> wanted
>>> to make
>>>  >the request fast, and the concatenating and string handling takes
>> some
>>> of that away.
>>>
>>> Unfortunately MySQL sprocs do not yet deliver this advantage.
>>>
>>> PB
>>>
>>> -----
>>>
>>> Magne Westlie wrote:
>>>> Hi,
>>>>
>>>> Thanks a lot Peter, that was useful and it worked fine. The only
>>>> problem is that the query I actually want to use this in, is a 100
>>>> line query with lots of arguments. I don't feel to good about
>> creating
>>>> it into a bunch of strings (16) that I have to concatenate with the
>>>> variables inbetween.
>>>> Also, I was moving the query into a stored procedure because I
>> wanted
>>>> to make the request fast, and the concatenating and string handling
>>>> takes some of that away.
>>>>
>>>> Is there another way?
>>>>
>>>> Magne
>>>>
>>>>
>>>>
>>>> Peter Brawley wrote:
>>>>> Magne,
>>>>>
>>>>> Sorry, the server is down at the moment, here is the entry ...
>>>>>
>>>>> To have an sproc accept a variable-length parameter list for an
>>>>> |IN(...)| clause in a query, code the sproc to |PREPARE| the query
>>>>> statement:
>>>>> |
>>>>> DROP PROCEDURE IF EXISTS passInParam;
>>>>> DELIMITER |
>>>>> CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
>>>>> VARCHAR(1000) )
>>>>> BEGIN
>>>>>  SET @qry = CONCAT( qry, param, ')' );
>>>>>  PREPARE stmt FROM @qry;
>>>>>  EXECUTE stmt;
>>>>>  DROP PREPARE stmt;
>>>>> END;
>>>>> |
>>>>> DELIMITER ;
>>>>> |
>>>>> For this example, the query string should be of the form:
>>>>> |
>>>>> SELECT ... FROM ... WHERE ... IN ( |
>>>>> but so long as it has those elements, it can be as complex as you
>>>>> like. When you call the sproc:
>>>>> 1. Quote each argument with a /pair/ of single quotes,
>>>>> 2. Separate these quoted arguments with commas,
>>>>> 3. Surround the whole |param| string with another set of single
>> quotes:
>>>>> |
>>>>> CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
>>>>> ('''abc'',''def'',''ghi''' ); |
>>>>> ||
>>>>> ||||PB
>>>>>
>>>>>
>>>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>> infoshop.com
> 
> 
> 
> 
> 
Thread
stored procedure, parameter type help neededMagne Westlie7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
    • Re: stored procedure, parameter type help neededMagne Westlie7 Feb
      • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
        • Re: stored procedure, parameter type help neededMagne Westlie8 Feb
          • RE: stored procedure, parameter type help neededJerry Schwartz8 Feb
            • Re: stored procedure, parameter type help neededMagne Westlie11 Feb
              • Re: stored procedure, parameter type help neededMoon's Father12 Feb