3 ms·
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.ke
by ars 18y 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 18y agoI 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 18y agoIf 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 18y agoof course. i wasn't certain from context that you were running it against an non-primary key.