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 Question regarding SubSelect Performance
Date Thu, 27 Sep 2012 13:00:20 GMT
Hello,

I have a problem with the following issue: I have two (three) tables, one contains documents
(document_table) and another one (document_attribute_table) contains attributes for these
documents (The third table contains the definition of the attributes). Now i want to sort
documents regarding to a specific attribute, the problem is, documents which does not have
this particular attribute should be considered by this too.

Simple example of the 2 tables:

document_table:
id  | name
-------------------
0   | doc1
1   | doc2
-------------------

document_attribute_table:
id  | referring_document    | referring_attribute   | attribute_value
-----------------------------------------------------------------------
0   | 0                     | 1                     | value1
1   | 0                     | 2                     | value2
2   | 1                     | 1                     | value3
-----------------------------------------------------------------------

The following select statement does what is intended:

SELECT * FROM document_table LEFT JOIN (SELECT * FROM document_attribute_table WHERE referring_attribute=2)
alias on alias.id=document_table.id order by alias.attribute_value

However this statement is horribly slow - now i'm looking for a "better" solution to this
problem.


Mime
View raw message