CODEWITHSUNDEEP
sqloracle-databasejoinwhere-clause
bw1984
bw1984

Reputation: 67

WHERE claus after JOIN is slowing down my query

My Query:

SELECT source.name, file_info.*  
from FILE_INFO  
JOIN source  
ON source.id = file_info.source_ID  
where file_info.source_ID in 
     (select ID from Source where name like 'Donatello.%');

My Tables:

FILE_INFO    
FILE_NAME | FILE_ID | SOURCE_ID | DATE_SEEN | CURRENT_STATUS  

SOURCE  
NAME | ID | CATEGORY

Background:

In my database, files are associated with the sources who provided them. Each file is given a FILE_ID and each source has a ID (same as SOURCE_ID in table FILE_INFO). However, the table FILE_INFO doesn't hold the name associated with the SOURCE_ID.

I'm trying to print all lines where from the table FILE_INFO along with the respective SOURCE name furthermore, i only want lines where the source providing that file starts with "DONATELLO".

This query works for me, however it runs very slow. Is there a better approach on my code? It works fine until I add that last 'where' clause. When that's included, it runs quite slow.

I appreciate your input.

Upvotes: 0

Views: 108

Answers (5)

Bohemian
Bohemian

Reputation: 425083

Change the condition to a simple one and invert the table order:

SELECT source.name, file_info.*  
from source   
JOIN FILE_INFO
  ON source.id = file_info.source_ID  
where source.name like 'Donatello.%'

With indexes in source(name) and file_info(source_ID).

This will be faster because the where clause can be spied to the first table in the join table list so indexes can be used with the condition.

Upvotes: 0

myselfhimself
myselfhimself

Reputation: 579

What about the following ?

SELECT source.name, file_info.*  
from FILE_INFO  
JOIN source  
ON source.id = file_info.source_ID and source.name like 'Donatello.%';

Upvotes: 0

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Do you have an index on name column from source table?

Why don't you just write your query like that:

SELECT source.name, file_info.*  
from FILE_INFO  
JOIN source  
ON source.id = file_info.source_ID  
where source.name like 'Donatello.%';

Upvotes: 1

Mureinik
Mureinik

Reputation: 311528

You IN clause uses a sub query, and in queries the source table again.

Why not simply your SQL as follows?

SELECT source.name, file_info.*  
from FILE_INFO  
JOIN source  
ON source.id = file_info.source_ID  
where source.name like 'Donatello.%';

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Would this not be the same?

SELECT source.name, file_info.*  
FROM FILE_INFO  
    JOIN source  
        ON source.id = file_info.source_ID  
WHERE source.name like 'Donatello.%'

Upvotes: 2

Related Questions