[CLUG-tech] SQL Query Question

Izak Burger isburger at gmail.com
Wed Dec 19 13:15:51 SAST 2007


On Dec 19, 2007 8:24 AM, Chris Meistre <meistrch at intekom.co.za> wrote:
> > SELECT a.url, a.txt FROM table1 AS a LEFT JOIN table2 AS b ON a.url = b.url WHERE b.url IS NULL;
> >
> I tried this, with not too much speed improvement.

It should be slightly faster, but it will still be slow. Whenever you
do an outer join it causes a full table scan to be done. This is
because it HAS to consider all rows, because you told it to. It
doesn't even LOOK at your indexes, so it is always notoriously slow.

For this reason, I now advise the SQL programmer person/wife in my
life to add a boolean column to table1 that you set to true once a
corresponding row is added in table2. This violates what you've
learned in Computer Science (telling you that this is duplication) but
really is the only sane way to get some speed back. If you are worried
about inconsistency, add a few triggers. Since the triggers only look
for a specific row, they use indexes and are quite fast. I know some
people object to the use of triggers, but seriously, this is what they
are there for. In postgresql at least, most constraints are
implemented using triggers anyway.

regards,
Izak


More information about the clug-tech mailing list