Question: I have a table with 3 columns:

```
item_id (decimal), key_name (varchar), key_string_value (varchar)

```

It is populated with hundreds and thousands of rows. Below are the first 6 rows to give you an idea of the data.

```
 1. 1              product              product1
 2. 1              topic                topic1
 3. 1              segment              segment1
 4. 2              product              product2
 5. 2              topic                topic1
 6. 2              segment              segment1

```

Each distinct `item_id` has 3 rows associated with it and describing it with metadata like topic, product, segment. I am trying to write a SQL query which does the following and performs extremely well.

Now I need to select all item\_ids which will suffice a certain combination of these metadata for example:

* get me all `item_id`'s for which `topic='topic1'` and `product='product2'` and `segment='Segment1'`

I am trying both self joins and correlated queries but am not sure which direction should I continue for the best performance. Any help would be appreciated.

Answer: 
You can use `HAVING` and conditional aggregation for this:

```
SELECT item_id
FROM YourTable
GROUP BY item_id
HAVING MAX(CASE WHEN key_name = 'topic' AND key_string_value ='topic1' THEN 1 END) = 1
  AND  MAX(CASE WHEN key_name = 'product' AND key_string_value ='product2' THEN 1 END) = 1
  AND  MAX(CASE WHEN key_name = 'segment' AND key_string_value ='segment1' THEN 1 END) = 1

```

Is it the most performant? Will have to test, but I'd expect it to beat self-joins and correlated subqueries.
First of all: I don't think this has a well-performing solution without an index on `item_id`, so I think you should change your PK to a composite of `(item_id, key_name)` - you need this to be unique anyway.

Assuming you have done so, we now have a fast track to both `item_id` and `key_name`, which I consider to be a necessity.

We can now try a self join, which should give good results on MySQL, as in InnoDB all composite indices are clustered:

```
SELECT
  one.item_id 
FROM table_name AS one
INNER JOIN table_name AS two 
  ON two.item_id=one.item_id
INNER JOIN table_name AS three 
  ON three.item_id=one.item_id
WHERE one.key_name='product'
AND one.key_string_value='product1'
AND two.key_name='topic'
AND two.key_string_value='topic1'
AND three.key_name='segment'
AND three.key_string_value='segment1'
;

```

It is important to apply the selector with the highest selectivity to the driving table. i.e. `one` - in my query I have assumed, that `product` has a higher selectivity than `topic` or `segment`.