5 ms·

MySQL, Sub-selects and IN()

troels 15y ago
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.
- forkandwait 15y ago
  May I suggest using Postgres?
  - troels 15y ago
    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.
- deleted 15y ago
  [deleted]
Hawramani 15y ago
I wonder if NOT NOT IN would have solved it since he mentions the problem doesn't exist for NOT IN.
- jswinghammer 15y ago
  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.
  - Hawramani 15y ago
    Would using parentheses (NOT(NOT IN...)) make a difference?
    - jswinghammer 15y ago
      Using NOT NOT IN is a syntax error in any event. I was answering in the hypothetical in any event.
      - Hawramani 15y ago
        Ah I see. Thanks for the answer.
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 ago
  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...
  - encoderer 15y ago
    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.
    - moe 15y ago
      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.
      - IgorPartola 15y ago
zzzeek 15y ago
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.
- thibaut_barrere 15y ago
  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.
  - chrisjsmith 15y ago
    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.
    - thibaut_barrere 15y ago
      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 :)
      - chrisjsmith 15y ago
        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.
    - rfrey 15y ago
      Downvoted because "anybody who uses <x> is stupid" comments aren't useful - first sentence is just that.
tedjdziuba 15y ago
FWIW this is why the rest of us chuckle at 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.

forkandwait 15y ago
May I suggest using Postgres?
- troels 15y ago
  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.
deleted 15y ago
[deleted]

Hawramani 15y ago

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

jswinghammer 15y ago
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.
- Hawramani 15y ago
  Would using parentheses (NOT(NOT IN...)) make a difference?
  - jswinghammer 15y ago
    Using NOT NOT IN is a syntax error in any event. I was answering in the hypothetical in any event.
    - Hawramani 15y ago
      Ah I see. Thanks for the answer.

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 ago
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...
- encoderer 15y ago
  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.
  - moe 15y ago
    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.
    - IgorPartola 15y ago

zzzeek 15y ago

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.

thibaut_barrere 15y ago
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.
- chrisjsmith 15y ago
  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.
  - thibaut_barrere 15y ago
    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 :)
    - chrisjsmith 15y ago
      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.
  - rfrey 15y ago
    Downvoted because "anybody who uses <x> is stupid" comments aren't useful - first sentence is just that.

tedjdziuba 15y ago

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