From: "Orlando Andico" <[EMAIL PROTECTED]> Subject: Re: [plug] Re: Open-source database evaluation
> seriously, subqueries particularly NOT IN type are horrible for > performance (better to use an outer join in such cases). Many queries using NOT IN can usually be rewritten to use NOT EXISTS, which will facilitate use of indexes. SELECT item FROM table1 WHERE item NOT IN (SELECT item from table2); can be rewritten as SELECT item FROM table1 WHERE NOT EXISTS (SELECT item from table2 where item.table2=item.table1); The first query will obviously be very slow because of repeated sequential scans of table2 -- one for each row of table1! The second query will usually utilize an index on table2's item column. > Better yet, > forget about subqueries altogether and learn how to use joins effectively. Of course it will always depend on your datamodel, indexes and statistics, how your query planner works, how many records you have, etc. There's usually more than 1 way to get the data you need. Explain is your friend. But then again, sometimes it's faster to dump everything to flat files and use grep. :-) Brian _ Philippine Linux Users Group. Web site and archives at http://plug.linux.org.ph To leave: send "unsubscribe" in the body to [EMAIL PROTECTED] Fully Searchable Archives With Friendly Web Interface at http://marc.free.net.ph To subscribe to the Linux Newbies' List: send "subscribe" in the body to [EMAIL PROTECTED]
