Searching against Entity-Attribute-Value data

Question

What's the best way to do searches against EAV data? Given a dozen fields that are stored in this table, I need to be able to search any combination of them: Last Names of "Jones" and City of "Springfield" and Status of "1". There's a search form that can have any fields filled out.

Obviously the final version will be a little more dynamic, but here's what I've got so far:

SELECT DISTINCT Utility.ClientSearch.ClientId AS ClientId,
    cs1.FieldValue AS FirstName, 
    cs2.FieldValue AS City
FROM Utility.ClientSearch
LEFT OUTER JOIN Utility.ClientSearch AS cs1 
    ON cs1.ClientId = Utility.ClientSearch.ClientId 
    AND cs1.FieldName = 'LastName' 
LEFT OUTER JOIN Utility.ClientSearch AS cs2 
    ON cs2.ClientId = Utility.ClientSearch.ClientId 
    AND cs2.FieldName = 'City'
WHERE (cs1.FieldValue = 'Jones') 
    AND (cs2.FieldValue = 'Springfield')

It seems fast against nearly a million records, but I'd like to know if this is the most efficient method, or if there's something better.

I realize that EAV isn't a good idea, but it's not my application. I'm just writing a query wrapper around somebody else's application. I have no control over the application or it's data structures.

Answer 1

I'm not sure why you have to self join 3 times... the first table does not serve a purpose.

SELECT DISTINCT cs1.ClientId AS ClientId,
    cs1.FieldValue AS FirstName, 
    cs2.FieldValue AS City
FROM Utility.ClientSearch AS cs1 
INNER JOIN Utility.ClientSearch AS cs2 
    ON cs1.ClientId = cs2.ClientId 
WHERE cs1.FieldName = 'LastName' 
    AND cs1.FieldValue = 'Jones'
    AND cs2.FieldName = 'City'
    AND cs2.FieldValue = 'Springfield'

If your table gets so big that you start seeing performance problems, look into table partitioning on ClientID