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]
