Jo Colina Jo Colina - 1 month ago 7
SQL Question

MYSQL Subrequest too long

I have a very simple subquery, but I think I'm missing something, because MySQL runs it forever.
Let's say I have two tables, a

FruitGenre
and a
FruitFlavor


FRUIT GENRE
| id | name |
| 1 | apple |
| 2 | orange |

FRUIT FLAVOR
| fruit_id | flavor |
| 1 | cherry |
| 2 | orange |


Yeah, you can have Cherry flavored apples. But any flavor is linked to only one fruit (there cannot be a Cherry flavoured Cherry for example).

My request is as follows:

SELECT * FROM FruitGenre WHERE id IN (SELECT fruit_id FROM FruitFlavor where FruitFlavor.flavor='cherry')


However, I can wait an hour and still get no respose. But if I split the request in two, I get immediate responses.

Spliited:

SELECT fruit_id FROM FruitFlavor where FruitFlavor.flavor='cherry';


SELECT * FROM FruitGenre WHERE id IN (id_written_on_paper_from_previous_request)


Any help will be appreciated.

Answer

Can you try :

SELECT FG.* FROM FruitGenre FG
left join FruitFlavor FF
on FF.id = FG.id
where FF.flavor='cherry'

And If you add index in FF.flavo. the query will be faster