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 element_id=@tmp;
+------------+--------------+------------+
| 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;
+------------+---------------+------------+
| element_id | display_name | data_units |
+------------+---------------+------------+
| 3 | Sync | |
| 4 | Graph Samples | V |
| 5 | First E | V |
| 7 | Graph Sample | V |
| 8 | Test Graph | V |
+------------+---------------+------------+
5 rows in set (0.00 sec)

mysql>

The problem is that when I try to use a variable that is a string with
OR's contained, it only uses the first one. Anybody know what is going on
here?

David Godsey

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 element_id=@tmp;

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

Thank you Baron, I'll give that a shot, and thanks for the IN() list tip
.... much appreciated.

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 element_id=@tmp;
>
> 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
>

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 element_id=@tmp;
>
> 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
>