Just use the type varchar.I always use it in my regular life. On Feb 11, 2008 4:44 PM, Magne Westlie <[EMAIL PROTECTED]> wrote:
> > > 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/[EMAIL PROTECTED] > >> infoshop.com > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
