CODEWITHSUNDEEP
sqlsql-serversubqueryquery-optimization
nick-m
nick-m

Reputation: 13

Optimization of a sql-query with exists

I have a table:

+----+---------+-----------+--------------+-----------+
| id | item_id | attr_name | string_value | int_value |
+----+---------+-----------+--------------+-----------+
|  1 |       1 |         1 | prop_str_1   | NULL      |
|  2 |       1 |         2 | prop_str_2   | NULL      |
|  3 |       1 |         3 | NULL         | 2         |
|  4 |       2 |         1 | prop_str_1   | NULL      |
|  5 |       2 |         2 | prop_str_3   | NULL      |
|  6 |       2 |         3 | NULL         | 2         |
|  7 |       3 |         1 | prop_str_4   | NULL      |
|  8 |       3 |         2 | prop_str_2   | NULL      |
|  9 |       3 |         3 | NULL         | 1         |
+----+---------+-----------+--------------+-----------+

And I want to select item_id with specific values for the attributes. But this is complicated by the fact that the fetching needs to do on several attributes. I've got to do it just using exists:

select *
  from item_attribute as attr
  where (name = 1 and string_value = 'prop_str_1')
  and exists
  (select item_id
  from item_attribute
  where item_id = attr.item_id and name = 2 and string_value = 'prop_str_2')

But the number of attributes can be increased, and therefore nested queries with exists will increase. How can I rewrite this query to reduce the nested queries?

UPD:

create table item_attribute(
    id int not null,
    item_id int not null,
    attr_name int not null,
    string_value varchar(50),
    int_value int,
    primary key (id)
);

insert into item_attribute values (1, 1, 1, 'prop_str_1', NULL);
insert into item_attribute values (2, 1, 2, 'prop_str_2', NULL);
insert into item_attribute values (3, 1, 3, NULL, 2);
insert into item_attribute values (4, 2, 1, 'prop_str_1', NULL);
insert into item_attribute values (5, 2, 2, 'prop_str_3', NULL);
insert into item_attribute values (6, 2, 3, NULL, 2);
insert into item_attribute values (7, 3, 1, 'prop_str_4', NULL);
insert into item_attribute values (8, 3, 2, 'prop_str_2', NULL);
insert into item_attribute values (9, 3, 3, NULL, 1);

Upvotes: 1

Views: 3008

Answers (1)

DRapp
DRapp

Reputation: 48179

See if this works for you. It in essence does the same thing... Your first qualifier is that a given attribute name = 1 and string = 'prop_str_1', but then self-joins to attribute table again on same ID but second attribute and string

select 
      attr.*
   from 
      item_attribute attr
         JOIN item_attribute attr2
            ON attr.item_id = attr2.item_id
            and attr2.name = 2
            and attr2.string_value = 'prop_str_2'
   where 
          attr.name = 1 
      and string_value = 'prop_str_1'

I would also have an index on your table on (name, string_value, item_id) to increase performance of where and join conditions.

Upvotes: 1

Related Questions