4 ms·

Yeah, LEFT JOIN ... IS NULL is problematic. If you don't have a true 1:M relationship between the two tables, you could get cartesian sub-products in the result

Yeah, LEFT JOIN ... IS NULL is problematic. If you don't have a true 1:M relationship between the two tables, you could get cartesian sub-products in the result set. If do have 1:M relationship, but the database doesn't know about it, it still has to check for cardinality on both sides. Even if you have declared foreign keys, the optimizer might not know how to use that information. The basic rule is, don't ask the database to do more than is necessary. Don't include tables in the FROM clause that won't be used downstream (GROUP BY, HAVING, SELECT, ORDER BY). If you only need it in the WHERE clause, try using a correlated subquery (ie, EXISTS). Another example for finding duplicates: select * from table1 as a where exists ( select * from table1 as b group by key1 having count(*) > 1 and a.key1 = b.key1 ) (Not sure if this syntax works on MySQL, but there's the idea.)

ars 17y ago
That works fine in MySQL, but assuming you have an index on (key1, PRIMARY_KEY) this is faster: select distinct a.* from table1 a join table1 b on (a.key1 = b.key1 and a.PRIMARY_KEY != b.PRIMARY_KEY) Yours took 0.59 sec, and mine was 0.10 sec (on some random test data). In general MySql doesn't do so well with subqueries - it likes them as joins. But in this case I think any database would be faster with my query (anyone care to test)? And when I say joins instead of subqueries I mean: select * from table1 as a join (select key1 from table1 as b group by key1 having count(*) > 1 ) c on (a.key1 = c.key1) Which took 0.00 sec (must say I was not expecting that).
- pradocchia 17y ago
  I have to say, I don't understand your join in the first example. In my head, key1 was the primary key. In the second example, if key1 is unique and the database knows it, the optimizer should be able to determine that c returns no records, and therefore the outer query returns no records as well. It bypasses execution entirely, thus 0.00 sec. If key1 is not unique, or it is unique but the database doesn't know about it, that is impressive.
  - ars 17y ago
    If key1 is the primary key then how can you have duplicates of it? By definition it's not a unique key, and c does return records.
    - pradocchia 17y ago
      of course. i wasn't certain from context that you were running it against an non-primary key.

ars 17y ago

That works fine in MySQL, but assuming you have an index on (key1, PRIMARY_KEY) this is faster: select distinct a.* from table1 a join table1 b on (a.key1 = b.key1 and a.PRIMARY_KEY != b.PRIMARY_KEY) Yours took 0.59 sec, and mine was 0.10 sec (on some random test data). In general MySql doesn't do so well with subqueries - it likes them as joins. But in this case I think any database would be faster with my query (anyone care to test)? And when I say joins instead of subqueries I mean: select * from table1 as a join (select key1 from table1 as b group by key1 having count(*) > 1 ) c on (a.key1 = c.key1) Which took 0.00 sec (must say I was not expecting that).

pradocchia 17y ago
I have to say, I don't understand your join in the first example. In my head, key1 was the primary key. In the second example, if key1 is unique and the database knows it, the optimizer should be able to determine that c returns no records, and therefore the outer query returns no records as well. It bypasses execution entirely, thus 0.00 sec. If key1 is not unique, or it is unique but the database doesn't know about it, that is impressive.
- ars 17y ago
  If key1 is the primary key then how can you have duplicates of it? By definition it's not a unique key, and c does return records.
  - pradocchia 17y ago
    of course. i wasn't certain from context that you were running it against an non-primary key.