MySQL, Sub-selects and IN() | Hacker News Reader

MySQL, Sub-selects and IN()

johnewart.net

In plain english, basically you expected mysql to work from inner to outer parentheses but instead

   IN (SELECT species_id FROM enemy_species WHERE enemy_id = 123)

is executed against every row in the table instead of just once, taking a REALLY long time.

I think every everyone goes through that particular mysql learning curve - use a join instead.

+1 Very true, we should not read queries as math expressions. As I wrote there, the reason being is that mysql's query optimizer rewrites this query into EXISTS (SELECT 1) which will run for each row of the outer query.

Sorry, but that is typical MySQL attitude - when someone finds a bug or a missing feature, they just say "you shouldn't do it that way".

Same thing they were saying back in the '90s for foreign keys, transactions, stored procedures...

So it's wrong to work-around and teach-around the limitations of any given tool?

I have to do the same when I teach new developers and friends about the replication problems with PG.

I have to do the same when I teach new developers and friends about the replication problems with PG.

When you say "replication problems" then you probably mean silent slave corruption?

I'm sorry but this is still a MySQL-only feature. The PostgreSQL-replication can't go out of sync, and worse, it can't even silently fail.

In this regard (and some others) PostgreSQL sadly lacks all the fun and excitement that can be had with MySQL.

Could you elaborate on this?

The gist is that postgres replication, by design[1], can not go out of sync.

This is a major difference to MySQL replication, where silent divergence between Master/Slave is a common problem. You don't notice it until you run into spurious SQL errors or perform a manual table-checksum. In a worst case you don't notice it until that day when your master fails...

[1] http://awurl.com/0g7Eshe5L#first_awesome_highlight

There have been promises to fix this forever. They know it's a problem. It's just not a high priority fix.

Um, it's not a bug - how would you purposely execute a subquery against every row in a table?

It's doing exactly what you told it to do.

The bug is a human bug of not understanding what you are asking even though it seems logical.

If it's impossible then why do both Oracle and SQL Server handle this scenario correctly? I've just tried it on both.

(I do know the answer - it's because they can do hash joins and MySQL can't).

Except that in the specific example I have (this is my post), the results of the inner select (I would assume) should be cached because the condition in the inner select is a fixed set of criteria, not related at all to the outer records. Also of interest is that NOT IN(insert subselect here) doesn't raise the same issue. Additionally it prevents the entire query from using any indices... I have lots of interesting query planner results from this particular go around.

Also it's been fixed in 6.0-alpha just not back-ported, so I'm not sure I agree here.

i think i noticed this problem when i was writing a query two months ago.

back around 2001, I was reading Celko's "SQL for Smarties"... MySQL didn't have subselects then so I rewrote more than half of the subselect queries with joins... The funny thing is that I'd run my converted queries in Oracle and find they'd run faster in Oracle if i did them that way.

If you're looking to actually think relationally, that is, composing more complex queries from simpler components, as opposed to using only find-row-by-id types of queries (the kind that work equally well with a noSQL store like mongo anyway), you'd best use Postgresql, or if you like to spend money for no good reason an Oracle or SQL Server.

What makes you tell money spent on SQL Server is spent for no good reason ? (real question)

Afaik a lot of .Net shops I know are fairly happy with it, to say the least.

I don't think any .net shops are particularly happy with it - they know of no other tool.

My personal opinion: There are far less mentally and emotionally demanding things that do as good a job for zero outlay and don't suddenly steal your wallet whenever you try and scale them up.

Having said that, I've used it since SQL 6.5 and it supports small to medium workloads fine. It's also significantly less ugly and needy than Oracle.

I'd pick PostgreSQL if I was going solo on a project though.

The .Net shops I know are aware of other tools (including MySQL, Redis, MongoDB...), so I guess we're not talking about the same shops.

Thanks for the rest of your feedback though :)

The ".net shop" I work for uses MySQL, MongoDB as well. But it's a rarety having done a lot of contract work over the years.

Downvoted because "anybody who uses <x> is stupid" comments aren't useful - first sentence is just that.

SQL Server is an excellent product, tho' even I would not have gone 24/7 with it prior to 2005. Comparing the free version to MySQL it's no contest - SQL Server is faster, supports far richer SQL and has much, much better instrumentation for tuning and diagnosis.

And licenses for MySQL Enterprise aren't that cheap either.

The comparison is between PostgreSQL and SQL Server, not SQL Server and MySQL.

Oh yes, MySql is awkward. You get used to some of those subtleties after a while, but it would be nice if you didn't have to.

May I suggest using Postgres?

You know. After a while you just get used to the quirks. Postgres has its own oddities. Granted they have a tendency to be by design, rather than glaring errors, but can still be annoying.

I wonder if NOT NOT IN would have solved it since he mentions the problem doesn't exist for NOT IN.

No. MySQL is able to optimize an anti-join but not the IN condition. It works about the same as having a LEFT JOIN with a not is null check in the where clause. MySQL will see NOT NOT IN as just IN.

Would using parentheses (NOT(NOT IN...)) make a difference?

Using NOT NOT IN is a syntax error in any event. I was answering in the hypothetical in any event.

Ah I see. Thanks for the answer.

FWIW this is why the rest of us chuckle at MySQL.

Reply on news.ycombinator.com