Prepared Statements with the IN function worked beautifully. Performance
is slightly degraded, but I get the functionality I want and can live with
the slight performance hit.
Thanks again.
Dave
> Hi Dave,
>
> Dave G wrote:
>> Hello all,
>>
>> I'm trying to do some processing on the front end to optimize my query
>> on
>> the backend. I would like to generate a list of id's for this query
>> like
>> so:
>>
>> SELECT REPLACE('3,4,5,6,7,8,9',',',' OR element_id=') INTO @tmp;
>>
>> Then use it like:
>>
>> mysql> select @tmp;
>> +---------------------------------------------------------------------------------------------------+
>> | @tmp
>> |
>> +---------------------------------------------------------------------------------------------------+
>> | 3 OR element_id=4 OR element_id=5 OR element_id=6 OR element_id=7 OR
>> element_id=8 OR element_id=9 |
>> +---------------------------------------------------------------------------------------------------+
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from display__Element where [EMAIL PROTECTED];
>
> MySQL sees that element_id is a number and coerces @tmp into a number.
> Check the
> output of 'show warnings' immediately after this statement. Another way
> to see what's
> happening is to run 'select 0 + @tmp'. MySQL is truncating after the
> first non-digit
> characters.
>
> What you want to do is use a prepared statement; prepare the statement as
> concat("select...", @tmp) and then execute the result.
>
>> +------------+--------------+------------+
>> | element_id | display_name | data_units |
>> +------------+--------------+------------+
>> | 3 | Sync | |
>> +------------+--------------+------------+
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from display__Element where element_id=3 OR element_id=4
>> OR element_id=5 OR element_id=6 OR element_id=7 OR element_id=8 OR
>> element_id=9;
>
> A better approach is to use an IN() list, which MySQL might even be able
> to optimize
> better:
>
> WHERE element_id IN(3,4,5,6,7,8,9);
>
> DANGER: do NOT use an IN() subquery, of the form
>
> WHERE element_id IN(select element_id from some_other_table)
>
> MySQL optimizes this kind of query very poorly.
>
> Baron
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
