Find items with maximum matching attributes

Question

Here is my table structure - table name "propAssign"

(indexed)        (composite index for attributeName and attributeValue)
 productId     attributeName     attributeValue 
    1              Height             3
    1              Weight             1
    1              Class              X1
    1              Category           C1
    2              Height             2
    2              Weight             2 
    2              Class              X2
    2              Category           C1
    3              Height             3
    3              Weight             1
    3              Class              X1
    3              Category           C1
    4              Height             4
    4              Weight             5
    4              Class              X2
    4              Category           C3

What I want to do is, get list of productId, sorted by maximum matching attributes-value pair. In real table, I am using numeric ID of attribute name and value, I've used text here for easy representation.

So if I want to find matching products of productId=1, I want it to look for product which has maximum match (like Height=3, Weight=1, Class=X1 and Category=C1). There may not be any with 100% match (all 4 match) but if there are, they should come first, next comes productId which has any 3 attributes matching, then any 2, etc.

I could add more indexes if required, better if I don't have to since there are millions rows. It's MariaDB v10 to be exact.

Desired result - If I try to find matching product for productId=1, it should return following, in same order.

productId
-----------
3
2

Reason - 3 has all attributes matching with 1, 2 has some matches and 4 has no match.

Answer 1

You can use conditional aggregation to retrieve the productId's with the highest number of matches first.

select productId, 
    count(case when attributeName = 'Height' and attributeValue='3' then 1 end)
    + count(case when attributeName = 'Weight' and attributeValue='1' then 1 end)
    + count(case when attributeName = 'Category' and attributeValue='C1' then 1 end) as rank
from mytable
group by productId
order by rank desc

The query above returns all rows even with 0 matches. If you only want to return rows with 1 or more matches, then use the query below, which should be able to take advantage of your composite index:

select productId, count(*) as rank
from mytable
where (attributeName = 'Height' and attributeValue = '3')
or (attributeName = 'Weight' and attributeValue = '1')
or (attributeName = 'Category' and attributeValue = 'C1')
group by productId
order by rank desc