mySQL query -- conditional select

Question

I have a table with some data

t1
------------------
id (int)
item_id (int)
item_description (text)
showHide (enum 0,1)

Normally I show any results that have showHide flag set to '0'. I need to modify the query to also show results with showHide flag set to '1' if user has the corresponding item_id in another table, t2.

t2
------------------
id (int)
item_id (int)
usr_id (int)

Stuck here, not sure how to do this:

SELECT item_description FROM t1
// get results with  showHide = 0 
// AND 
// showHide = 1 if t1.item_id found in t2 WHERE usr_id = 123

zerkms · Accepted Answer · 2011-01-20 23:57:13Z

1

Just combine 2 queries with UNION

SELECT t1.item_description
  FROM t1
 WHERE showHide = 0
UNION
    SELECT t1.item_description
      FROM t1
INNER JOIN t2 ON t1.item_id = t2.item_id
     WHERE showHide = 1

The more tricky way, thus less performant:

    SELECT t1.item_description
      FROM t1
 LEFT JOIN t2 ON t1.item_id = t2.item_id
     WHERE showHide = 0
        OR (showHide = 1 AND t2.item_id IS NOT NULL)

answered Jan 20, 2011 at 23:57

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mchl Over a year ago

I don't see how 'more tricky' implicates 'less performant'. I do not argue that the second solution might be actually performing worse (not until benchmarked), just don't see the conection between 'trickiness' (a subjective term to say the least) and performance.

zerkms Over a year ago

@Mchl: it is less performant because: 1) LEFT JOIN (which is slower than INNER JOIN 2) OR with 0/1 field (since mysql doesn't have bitmap type indexes) will not be optimized at all (it will be fullscan in any case).

zerkms Over a year ago

@Mchl: the connection between trick and performance in my mind is: the more simple and obvious query almost always works better. Proven for years.

Mchl Over a year ago

That's a generalisation however, that can lead you astray. KISS is a wonderful rule, but as any rule can be abused ;) Technical reasons you provided convince me more than argument from simplicity.

santa Over a year ago

Would it be easier if I ran two separate queries? In the first I can get all item_ids, turn then into a comma separated string to be used in the second query. The second query would get description with flag set to 0 + (1 if the ID is found IN IDs string)? I just think if both tables have thousands of line, it may take a while to process in one query.

|

Mchl · Accepted Answer · 2011-01-20 23:59:19Z

1

SELECT 
  t1.item_description
FROM
  t1
LEFT JOIN (
  SELECT item_id FROM t2 WHERE usr_id = 123
) AS t2
USING (item_id)
WHERE 
  t1.showHide = 0 
OR
  (t1.showHide = 1 AND t2.item_id IS NOT NULL)

answered Jan 20, 2011 at 23:59

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

5 Comments

zerkms Over a year ago

Nested select instead of regular join with ON can decrease performance dramatically.

Mchl Over a year ago

It's uncorrelated subquery. Unless you're working on MySQL 4.x it works fine.

zerkms Over a year ago

@Mchl: the fact that it is not correlated one doesn't mean that mysql will perform it just once. For mysql it is true, that something written in documentation is not absolutely true irl. Also - you lose the ability of using composite index (user_id + item_id) to make your query faster, and for looking for USING(item_id) you'll always get a scan over result set from nested query.

Mchl Over a year ago

Cool, I'll look into it, cause I use such queries quite often. Thanks.

zerkms Over a year ago

@Mchl: for first - compare EXPLAIN for them, but I bet you know it yourself ;-)

Collectives™ on Stack Overflow

mySQL query -- conditional select

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related