Help with optimising SQL query

Hi i need some help with this problem. I am working web application and for database i am using sqlite. Can someone help me with one query from databse which must be optimized == fast =)

I have table x:

ID | ID_DISH | ID_INGREDIENT
 1 | 1       | 2
 2 | 1       | 3
 3 | 1       | 8
 4 | 1       | 12

 5 | 2       | 13
 6 | 开发者_如何学JAVA2       | 5
 7 | 2       | 3

 8 | 3       | 5
 9 | 3       | 8
 10| 3       | 2

.... ID_DISH is id of different dishes, ID_INGREDIENT is ingredient which dish is made of: so in my case dish with id 1 is made with ingredients with ids 2,3

In this table a have more then 15000 rows and my question is:

i need query which will fetch rows where i can find ids of dishes ordered by count of ingreedients ASC which i haven added to my algoritem.

examle: foo(2,4) will rows in this order:

ID_DISH | count(stillMissing)
    10  |   2
    1   |   3    

Dish with id 10 has ingredients with id 2 and 4 and hasn't got 2 more, then is

My query is:

SELECT
    t2.ID_dish,
    (SELECT COUNT(*) as c FROM dishIngredient as t1
     WHERE t1.ID_ingredient NOT IN (2,4)
     AND t1.ID_dish = t2.ID_dish
     GROUP BY ID_dish) as c
FROM dishIngredient as t2
WHERE t2.ID_ingredient IN (2,4)
GROUP BY t2.ID_dish
ORDER BY c ASC

works,but it is slow....

select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing 
from x
group by ID_DISH
having stillMissing != count(*)
order by stillMissing 

this is the solution, my previous query work 5 - 20s this work about 80ms

This is from memory, as I don't know the SQL dialect of sqlite.

SELECT DISTINCT T1.ID_DISH, COUNT(T1.ID_INGREDIENT) as COUNT
FROM dishIngredient as T1 LEFT JOIN dishIngredient as T2 
ON T1.ID_DISH = T2.ID_DISH
WHERE T2.ID_INGREDIENT IN (2,4)
GROUP BY T1.ID_DISH
ORDER BY T1.ID_DISH