What is an efficient way to query against an entity-attribute-value model

Question

I'm working with a database set up like this:

  entity field       value
   1      start_date  June 1, 2010
   1      end_date    August 30, 2010
   1      artist      some name

And I want to query all the entities with artist name "some name" that have started but not ended.

I came up with something like this:

SELECT start.entity
FROM field_values AS `start`
  INNER JOIN field_values AS `end`
  INNER JOIN field_values AS `artist`
WHERE 
  (start.field = 'start_date' AND end.field = 'end_date' AND artist.field='artist')  AND 
  (STR_TO_DATE(start.value, '%M %d, %Y') < NOW() AND 
   STR_TO_DATE(end.value, '%M %d, %Y') > NOW())  AND 
  artist.value="some artist";

But this doesn't strike me as being incredibly efficient. Is there a better approach to this?

Answer 1

For clarity you can put the join clause items in the join clause, but in terms of query optimisation that is pretty much the way to do it.

You could consider rewriting the query to something like this though:

SELECT start.entity
FROM entity
  JOIN field_values AS start
    ON entity.id = start.entity AND start.field = 'start_date'
  INNER JOIN field_values AS end
    ON entity.id = end.entity AND end.field = 'end_date'
  INNER JOIN field_values AS artist
    ON entity.id = artist.entity AND artist.field = 'artist'
WHERE STR_TO_DATE(start.value, '%M %d, %Y') < NOW()
  AND STR_TO_DATE(end.value, '%M %d, %Y') > NOW()
  AND artist.value="some artist"
;

You could also normalize the field to save a little space (assuming that field is not an enum)

Answer 2

While EAV has it's place, you already have 3 somewhat guaranteed attribute names (as you are searching for it I assume a lot of entities share those attributes). Smells like a non-candidate for EAV, or a possible combination of attributes which may go in their seperate table artistdates (id,artist,start,end), which could possibly link in the EAV table as E=<whatever>,A=artistdate_id,V=<artistdate_id> if you like.

The power of EAV is for those situations in which the actual attributes present are not consistent, and which are usually queried by solely entity-id, or the existence of an attribute (possibly in combination with a value). As soon as you're looking for combinations of attributes performance will suffer and the question is whether those should live separately in the EAV structure or they should be split out into a 'traditional' row based table.