Joining 4 tables - Performance

[jujitsurob]

The Tables I have are...

Types Styles Contents
----- ------ --------
ID ID ID
Name Name Name


Items Types_Are Styles_Are Contents_Are
-------- --------- ---------- ------------
ID Item_ID Item_ID Item_ID
Name Type_ID Style_ID Content_ID
Status
Created
...
...


Items can obviously have more than 1 Type, Style, and Content, or even non of them.

Users will select Type(s), Style(s), and Content(s), sometimes they may not select any types, or styles, or contents.

I want a list of Item IDs to be returned that match these conditions.


Currently I run the select query as....

select distinct t1.id from items t1, types_are t2, styles_are t3, contents_are t4
where t1.status = 2
and t2.item_id = t1.id and (t2.type_id = 3)
and t3.item_id = t1.id and (t3.style_id = 3)
and t4.item_id = t1.id and (t4.content_id = 2 and t4.content_id = 4 and t4.content_id = 5)
order by rand().

But sometimes this takes around 5 minutes to run, and with only around 50 Items on the database.

Obviously this is a mega basic join, so how could performance be improved, what sort of join whould i be using?

An example would be of great help.

 

[r937]

well, one thing i spotted right away is this:

and t4.content_id = 2
and t4.content_id = 4
and t4.content_id = 5

this is never going to be true and therefore your query should return 0 rows always

you want

and ( t4.content_id = 2
or t4.content_id = 4
or t4.content_id = 5 )

which can be shortened to

and t4.content_id in ( 2 , 4 , 5 )

make sure each of the ID columns is declared primary key in the Types, Styles, Contents, and Items tables

create composite primary keys and "reverse" indexes for the many-to-many tables

e.g.

create table Types_Are
( Item_ID int not null
, Types_ID int not null
, primary key (Item_ID, Types_ID)
, index reverseTypex (Types_ID, Index_ID)
)

mysql can then use the indexes to improve performance

rudy

http://r937.com/

SQL Consulting