Query Help

[ruski]

Dear Friends,

 

I am trying to get some results from a table and here is what I am trying to do:

 

I have two tables:

1. a "user" table

2. a "users_items" table

 

I need to get a list of applicants which have certain records in the "user_items" table.

The structure of the "user_items" table is as follows:

 

id_user_item | id_user | id_item

 

So basically return a list of users which have a record with particular id_item. However the problem comes when I try to get users which have a set of items (multiple) in the table, which is proving difficult.

 

I have tryed to inner join the user_items table for each item required:

 

SELECT t1.*
FROM
    user AS t1
    INNER JOIN
    user_items AS t2
        ON(t1.id_app = t2.id_app and t2.id_item = 1)
     user_items AS t3
        ON(t1.id_app = t3.id_app and t3.id_item = 2)

 

The above query gives me the results I need but takes about 20 seconds to run (i have around 20k users and about 80k records in user_items table)

 

Could anyone please suggest a better way of doing this?

 

Thanks in advance

[jskywalker]

you might be missing an index, but you dont tell us which indexes you have.....