News for hackers. Stuff that matters.
Re:Learn SQL, dammit by obviouslygreen on Wednesday May 08, 2013 @02:48PM (#5676069)
I recently discovered that MySQL before 5.6 (5.5 being the latest available on RDS, of course) does not honor indexes if they should be invoked for a subquery, e.g.

select whatever from wherever where user_id in (select id from users where somethingorother like '%lol%');

Got an index on user_id? Too bad. Ignored.

If you precompute the values, though?

select whatever from wherever where user_id in (1, 2, 3);

Sweet, I love indexes! I'll definitely use them.

Re:Learn SQL, dammit by michaelt on Wednesday May 08, 2013 @04:08PM (#5676548)
Let's say your inner query returns n rows and wherever contains m rows. And your index is a B-tree, so you can hit it once in O(log m).

If you use a nested loop join, where you hit the index once for each inner query result, that's O(n*log m). If on the other hand you do a hash join, skipping the index but doing a full table scan of wherever, the complexity is O(m+n). So which is the faster choice depends on how many rows the inner query returns.

If you want to plan up front, before you know what m is, how would you decide which join to use?

Re:Learn SQL, dammit by miahi on Wednesday May 08, 2013 @04:03PM (#5676509)
Loks like it cannot evaluate the subquery result (number of items).