db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tino Schmidt <tino.schm...@medav.de>
Subject Re: Re: Question regarding SubSelect Performance
Date Fri, 28 Sep 2012 13:18:00 GMT
> Hi,
>
> I do not fully understand your query because you are joining
>
> document_table.id on 'alias.id' ?
> Given your columns names and your sample data set, I'd think you
> should join on alias.referring_document ? (the document_attribute_table
> is a N--N association intermediate table, isn't it ?
> The document_attribute_table.id column would be superfluous ?)
>
> In this case, why not a simple left join :
>   SELECT * FROM document_table doc
>   LEFT JOIN document_attribute_table da
>          ON da.referring_document = doc.id
>   WHERE da.referring_attribute = 2
>      OR da.referring_attribute IS NULL
>   ORDER BY da.attribute_value -- do NULL go first or last ?
>
> Regarding performances, I observed that you should either declare
> your foreign key constraints to Derby, or create an index on
> da.referring_document to speed up the join.
>
> HTH...

Hi,

sry this may create a new thread but I wasn't subscribed to the list until now and didn't
have the thread id.

Your select does not work, it omits the documents that do not have set the atttribute to search
for (doc 1 in the example) because the "IS NULL" statement does not work if this document
has another attribute set (and therefor an entry in the docuemnt_attribute_table exists).
The relevant columns already have indices and the foreign keys are defined correctly, the
problem is the suboptimal(?) query.

Greetings


Mime
View raw message