From: Magne Westlie (magnesymphonical.com)
Date: Mon Feb 11 2008 - 02:44:03 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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=jschwartzthe-
>> infoshop.com
>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]