Need help in optimising query

Question

I have two tables - incoming tours(id,name) and incoming_tours_cities(id_parrent, id_city)

id in first table is unique, and for each unique row from first table there is the list of id_city - s in second table(i.e. id_parrent in second table is equal to id from first table)

For example

incoming_tours

|--id--|------name-----|
|---1--|---first_tour--|
|---2--|--second_tour--|
|---3--|--thirth_tour--|
|---4--|--hourth_tour--|

incoming_tours_cities

|-id_parrent-|-id_city-|
|------1-----|---4-----|
|------1-----|---5-----|
|------1-----|---27----|
|------1-----|---74----|
|------2-----|---1-----|
|------2-----|---5-----|
........................

That means that first_tour has list of cities - ("4","5","27","74")

AND second_tour has list of cities - ("1","5")

---

Let's assume i have two values - 4 and 74:

Now, i need to get all rows from first table, where my both values are in the list of cities. i.e it must return only the first_tour (because 4 and 74 are in it's list of cities)

So, i wrote the following query

SELECT t.name
FROM `incoming_tours` t
JOIN `incoming_tours_cities` tc0 ON tc0.id_parrent = t.id
AND tc0.id_city = '4'
JOIN `incoming_tours_cities` tc1 ON tc1.id_parrent = t.id
AND tc1.id_city = '74'

And that works fine.

But i generate the query dynamically, and when the count of joins is big (about 15) the query slowing down.

i.e. when i try to run

SELECT t.name
FROM `incoming_tours` t
JOIN `incoming_tours_cities` tc0 ON tc0.id_parrent = t.id
AND tc0.id_city = '4'
JOIN `incoming_tours_cities` tc1 ON tc1.id_parrent = t.id
AND tc1.id_city = '74'
.........................................................
JOIN `incoming_tours_cities` tc15 ON tc15.id_parrent = t.id
AND tc15.id_city = 'some_value'

the query run's in 45s(despite on i set indexes in the tables)

What can i do, to optimaze it?

Thanks much

Answer 1

SELECT t.name
FROM incoming_tours t INNER JOIN 
  ( SELECT id_parrent
    FROM incoming_tours_cities
    WHERE id IN (4, 74)
    GROUP BY id_parrent
    HAVING count(id_city) = 2) resultset 
  ON resultset.id_parrent = t.id

But you need to change number of total cities count.

Answer 2

SELECT name
FROM (
      SELECT DISTINCT(incoming_tours.name) AS name,
             COUNT(incoming_tours_cities.id_city) AS c
      FROM incoming_tours
           JOIN incoming_tours_cities
                ON incoming_tours.id=incoming_tours_cities.id_parrent
      WHERE incoming_tours_cities.id_city IN(4,74)
            HAVING c=2
      ) t1;

You will have to change c=2 to whatever the count of id_city you are searching is, but since you generate the query dynamically, that shouldn't be a problem.

Answer 3

I'm pretty sure this works, but a lot less sure that it is optimal.

SELECT * FROM incoming_tours 
WHERE 
id IN (SELECT id_parrent FROM incoming_tours_cities WHERE id_city=4)
AND id IN (SELECT id_parrent FROM incoming_tours_cities WHERE id_city=74)
...
AND id IN (SELECT id_parrent FROM incoming_tours_cities WHERE id_city=some_value)