MySQL IN query support is lacking

I’ve been working with MySQL lately and was really surprised that it can’t do simple IN queries using a table as the IN recordset. One of these that I tried was later identified by the very system we’re writing as being a long running query (it had been running for over a day.) Fortunately you can write them using an inline table instead.

Doesn’t work:

select stuff from OutsideTable where id in (select id from InsideTable);

Works:

 select ot.stuff 
 from OutsideTable ot,
      (select id from InsideTable where something='value') it
 where ot.id=it.id;

Well, at least I could make it work.

(Oh, I didn’t run these queries, they’re just there to make the point).

Advertisements

3 responses to “MySQL IN query support is lacking

  1. Jed

    Driven here by your post on Oracle-L today, I saw this post and wondered. I am using MySQL 5.5.9 and have no problem at all executing queries like this (on InnoDB tables). Were you having trouble with the actual query function, or is it just the performance that was bugging you (last December)?

    Regards Nigel

    • Good question Nigel. By “doesn’t work” I mean it never returned. We noticed it was still running a day later. I imagine at some point it might have completed. So, the problem was really performance related.

    • It was the performance that was the issue. Our product discovered the long-running query still running something like 2 days later. We are using 5.5.1, so maybe they have this resolved! Thanks for the info.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s