4 ms·
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 =
by ck2 15y ago
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.
- dejan 15y ago+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.
- gaius 15y agoSorry, 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...
- encoderer 15y agoSo 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.
- moe 15y agoI 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.
- IgorPartola 15y agoCould you elaborate on this?
- moe 15y agoThe 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 http://awurl.com/0g7Eshe5L#first_awesome_highlight
- jswinghammer 15y agoThere have been promises to fix this forever. They know it's a problem. It's just not a high priority fix.
- ck2 15y agoUm, 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.
- gaius 15y agoIf 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).
- soysamurai 15y agoExcept 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.
- PaulHoule 15y agoi 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.