MySQL SELECT within SELECT taking forever...

[Big_Pat]

So what songs in my collection are cover versions of Bob Dylan songs? I wrote this query to find out:

$query = "Select artist from songlist
            where title in
            (Select distinct title
            from songlist
            where artist='Bob Dylan')";

but running it times out. If I substitute the inner select for a simple list of, say, three or four of his songs, the query runs fine so I can only assume that the thousands of permutations are what's slowing things down. Is there a more efficient way of achieving the same thing? Ultimately I'd like to replace 'Bob Dylan' with a variable, but first things first.

 

Thanks!

[jazzman1]

With this database design (we were talking about before) I think, it's the best you can do.

[Barand]

try a JOIN

Select artist from songlist
            inner join
            (Select distinct title
            from songlist
            where artist='Bob Dylan') as dylan
            using (title)

[Big_Pat]

Thank you, Barry, that's worked a charm. Why does yours work and mine doesn't?

[Barand]

Efficiency. One table subquery called once versus a dependent subquery called for every row