python - How to do this mysql query where I want to query columns side by side? -

i've table structure this:

col1 col2 col3  1    1    1  1    2    1  1    3    1  2    10   1 

now have information in tuples in python (1,2) column 1 , (1,1) column 3.

i want query table such in select clause want correspond column 1 value column 3.

by mean

there 2 queries:

select * table col1 = 1 , col3 = 1; select * table col1 = 2 , col3 = 1; 

i want combine these , many entries have in tuples. can lots of ands , union not solution beyond 10 values.

your simplest method, that's not result in performance problems, going iterating across tuples , generating , condition each, enclosed in parentheses , joined or conditions, produce query thus:

select *  table  (col1 = 1 , col3 = 1)    or  (col1 = 2 , col3 = 1)    or  (...) 

you're right, though, in that won't scale particularly well. might instead first analyze tuples values you're using in clause, in order generate more compact condition which, each unique acceptable col1 value, or's acceptable col3 values, , ands them against col1 value. example, consider tuples (1, 1), (1, 2), (2, 1), (2, 3), , following query generated them:

select *  table  (col1 = 1 , (col3 = 1 or col3 = 2))    or  (col1 = 2 , (col3 = 1 or col3 = 3)) 

that's bit less messy result of first method, , requires fair bit more effort ahead of time, it's going execute faster, because , short-circuiting means database engine can more determine whether given row satisfy clause; instead of needing check each (col1 = @m , col2 = @n) condition individually every row, can check whether row's col1 value matches, , ignore rest of condition if doesn't.

(of course, mysql's query optimizer should doing you, , while it'd take thorough benchmarking certain, given obviousness of optimization suspect you're getting free. second form of query still more human-readable, though, may or may not matter you.)

anyway, if need across many tuples of acceptable values, you're going generating either 1 relatively complex query, or bunch of simple-minded ones; each approach has benefits , drawbacks, given requirements , input, tend doubt you're going find algorithm generates query that's both more compact, , still readable enough debug should misbehave.

if you're concerned performance, i'd suggest waiting worry until you've demonstrated performance problem exists. if have done that, check indexes on table you're querying; if haven't created index across columns you're using in condition, excellent time so.