Jari Fredriksson wrote:
>> James Harper wrote:
>>> The subquery returns a very small result set (0 or 1,
>>> assuming you use DISTINCT) and so isn't too inefficient.
>>> It's when you say 'WHERE NOT EXISTS (SOME QUERY WITH
>>> LOTS OF RESULTS)' that you start to really bog down
>> True, but if the outer query contains a very large number
>> of records, the inner query is executed over and over and
>> over and ... 
>>
>> This multiplies the small execution time by a large
>> number. 
>>
>> Think of it like a WHILE loop that executes a block of
>> code each time round. The inner block of code might be
>> quick, but if the loop goes round several million times,
>> the cumulative time becomes significant. 
>>
>> One general rule of thumb of SQL execution is that bulk
>> processing is almost always faster than processing
>> records individually in a loop. 
> 
> All right then. Then there is this version:
> 
> INSERT INTO Filename(Name)
> SELECT DISTINCT Name
> FROM batch AS
> WHERE Name NOT IN
> (
>     SELECT DISTINCT Name
>     FROM Filename
> );
> 
> I guess it depends on RDMS, but I wonder it it executes the subquery more 
> than once.
> 
> 
> 

The keyword EXPLAIN before the query should show how mysql would react.

There's some tools also mysql-administrator which can be use to detect / search 
point of slow


-- 

     Bruno Friedmann



------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing 
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users