Optimizing tricky SQL search query

Question

I am trying to come up with a simple, performant query for the following problem:

Let's say there are several entities (items) which all have a unique ID. The entities have a variable set of attributes (properties), which therefore have been moved to a separate table:

T_Items_Props
=======================
Item_ID  Prop_ID  Value
-----------------------
101      1        'abc'
101      2        '123'
102      1        'xyz'
102      2        '123'
102      3        '102'
...      ...      ...

Now I want to search for an item, that matches some specified search-criteria, like this:

<<Pseudo-SQL>>

SELECT Item_Id(s)
  FROM T_Items_Props
 WHERE Prop 1 = 'abc'
   AND Prop 2 = '123'
     ...
   AND Prop n = ...

This would be fairly easy if I had a table like Items(Id, Prop_1, Prop_2, ..., Prop_n). Then I could do a simple SELECT where the search criteria could simply (even programmatically) be inserted in the WHERE-clause, but in this case I would have to do something like:

SELECT t1.Item_ID
  FROM T_Items_Props t1
     , T_Items_Props t2
     , ...
     , T_Items_Props tn -- (depending on how many properties to compare)
   AND t1.Item_ID = t2.Item_ID
   AND t1.Prop_ID = 1 AND t1.Value = 'abc'
   AND t2.Prop_ID = 2 AND t2.Value = '123'
     ...
   AND tn.Prop_ID = n AND tn.Value = ...

Is there a better/simpler/faster way to do this?

@Mzf Yes, I have tried that. Works quite well in terms of readability and flexibility, but unfortunately is a whole lot slower. — marsze, Jun 13 '13 at 13:28

score 2 · Answer 1 · answered Jun 13 '13 at 12:58

2

To make the query more readable, you could do something like:

SELECT 
    t1.Item_ID
FROM 
    T_Items_Props t1
where convert(varchar(10), t1.Item_ID) + ';' + t1.Value in (
    '1;abc',
    '2;123',
    ...
)

NOTE: This assumes, that your IDs will not have more than 10 digets. It might also slow your query down, due to the extra type conversion and string concatanation.

answered Jun 13 '13 at 12:58

LuigiEdlCarno

2,410
2
21
37

I was looking for a way to make it faster, not to improve readability. This actually does the opposite. Besides, do you mean `convert(varchar(10), t1.Prop_ID) + ...`? Apart from that, nice solution! – marsze Jun 13 '13 at 13:04

score 1 · Answer 2 · answered Jun 13 '13 at 13:45

1

You could count the number of correct Props. This isn't very good in case there could be duplicates. E.g.:

Prop_ID = 1 AND Value = 'abc'
Prop_ID = 2 AND Value = '123'

and the table would look like:

T_Items_Props
=======================
Item_ID  Prop_ID  Value
-----------------------
101      1        'abc'
101      1        'abc'

this would then be true, although it shouldn't.

But if you wanna give it a try, here's how:

SELECT nested.* FROM (
SELECT item_id, count(*) AS c FROM t_items_props
WHERE ((prop = 1 AND value = 'abc')
OR (prop = 2 AND value = '123')
... more rules here ...)
GROUP BY item_id) nested
WHERE nested.c > 2 ... number of rules ...

answered Jun 13 '13 at 13:45

marty bourque

734
4
7
20

i would be very interessted in results. marsze could you post some reults if you've tried it? PS: the mentioned problem could probably be solved with distinct or another group by. – marty bourque Jun 13 '13 at 14:26
There should not be any duplicates, because *Item_ID + Prop_ID* would be the primary key. This works about as fast as my solution (though the results varied a lot). I think it's easier to modify though and using a subquery like this is definitely faster than using `HAVING count(...)`, so thanks! – marsze Jun 14 '13 at 07:20

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

You can use a join statement together with filtering or faceted search. It gives better performance because you can limit the search space. Here is a good example: Faceted Search (solr) vs Good old filtering via PHP?.

edited May 23 '17 at 10:32

Community

1
1

answered Jun 13 '13 at 13:06

Micromega

12,486
7
35
72

Thx, but I have already tried the combination of combining the `WHERE`-conditions with `OR` instead of `AND` and then adding `HAVING COUNT(IDs) < n`, but this too is a lot slower! – marsze Jun 13 '13 at 13:34
Then did you tried concatenation like luigi propose? IMO you cannot improve joins so much. It depends much on the data. – Micromega Jun 13 '13 at 13:38

score 0 · Accepted Answer · answered Jun 13 '13 at 13:09

I've offered this in a previous post of similar querying intentions. The user could have 2 criteria one time, and five criteria another and wanted an easy way to build the SQL command. To simplify the need of having to add FROM tables and update the WHERE clause, you can simplify by doing joins and put that criteria right at the join level... So, each criteria is it's own set added to the mix.

SELECT 
      t1.Item_ID
   FROM 
      T_Items_Props t1

         JOIN T_Items_Props t2
            on t1.Item_ID = t2.Item_ID
           AND t2.Prop_ID = 2
           AND t2.Value = '123'

         JOIN T_Items_Props t3
            on t1.Item_ID = t3.Item_ID
           AND t3.Prop_ID = 6
           AND t3.Value = 'anything'

         JOIN T_Items_Props t4
            on t1.Item_ID = t4.Item_ID
           AND t4.Prop_ID = 15
           AND t4.Value = 'another value'
   WHERE
          t1.Prop_ID = 1
      AND t1.Value = 'abc'

Notice the primary query will always start with a minimum of the "T1" property/value criteria, but then, notice the JOIN clauses... they are virtually the same so it is very easy to implement via a loop... Just keep aliasing the T2, T3, T4... as needed. This will start with any items that meet the T1 criteria, but then also require all the rest to be found too.

Thanks for bringing this probably equally performant but easier scalable solution to my attention! — marsze, Jun 13 '13 at 13:38
@Phpdna, actually no its not... The first instance is the qualifier for at least ONE criteria... The rest are joined on the same ID but looking for the additional criteria, otherwise the user would start at the top ITEM table instead of at properties... Why try to query ALL ITEMS if you know at a minimum of one criteria, then just join on top of that for the additional criteria.. The joins qualify for EACH CRITERIA that is desired... and if you did the down-vote, I dont believe it was justified. — DRapp, Jun 13 '13 at 14:00
@drapp: I don't understand? In the body is many self-joins? What is wrong about this? I didn't down-vote thr first qualifier maybe I didn't read his question? — Micromega, Jun 13 '13 at 14:09

Optimizing tricky SQL search query

4 Answers4