Mammoth amounts of self joins

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a table that contains keywords (Varchars) each one mapped to a
product. so the database schema is just an id column, product column and
keyword column.

I want to select the products that contain x,y,z keywords. Now if this query
involves many keywords I end up with a massive amount of self joins on the
keywords table, is there a better way to do this than self joins? What would
work perhaps is multiple unions where I could discard non duplicated rows
but you can't seem to do that. Any ideas... ?

Re: Mammoth amounts of self joins

sks wrote:

Quoted text here. Click to load it

Show us the queries you have come up with and why they don't work and
provide some sample data (does not have to be real data).  

Is there more than one table involved?  

What do you consider "many keywords"?

Why are you doing "self-joins"?

Do the keywords map to more than one product?

Is this for a school project or some job that you have?

Re: Mammoth amounts of self joins

Quoted text here. Click to load it

It's for a software package I write.

The keywords are used as meta data about an item. Each of the keywords is
for a particular attribute that is relevant for certain items only. This is
all definable by the user at runtime. Let's say we had a site selling DVDs,
the attributes in this case might be Director, Actor, and Genre. Each of
these attributes might have multiple values (or none).

Here is a simple schema to demonstrate




I might want to find all DVDs that had Clint Eastwood, or Tommy Lee Jones,
or Richard Burton in as Actor, and where Comedies, Action or Drama in Genre.

I could self join like this

select distinct i.* from item i join attributes_values av1 on
join attributes_values av2 on where (av1.value='Clint
Eastwood' or av1.value='Tommy Lee Jones' ......) and  av2.value='Comedy' or
av2.value='Drama' ....) where i.status='Live' .... (other item related
clauses here).

This runs extremely slowly when I have more than a few attributes. 9 or 10
is quite common on some items and then you could be searching for 5 to 10
values in each attribute.

My faster solution (and the one I use ) is to select the item column from
the attribute_values table and union it for each different attribute I am
searching against, then group that result and select from it where count is
equal to the number of attributes I searched for and then join that against
the item table. It's fast but I think ugly.

I am wondering if there is an obvious solution I have missed.

Re: Mammoth amounts of self joins

Quoted text here. Click to load it

This solution also scales much better than the self-join method.  Keep in
mind that MySQL has a limit of 31 joined tables per query (or 63 if you use
a 64-bit operating system).  It's likely that a 31-way join performs very

The Entity-Attribute-Value data model (or EAV) is hard to use right, and it
violates a few principles of relational modeling and normalization.

See also my reply to today's thread with subject, "Joining data from 2
fields into the first one".

Bill K.

Site Timeline