Hibernate version: 3.2.2
Mapping documents:
Code:
<hibernate-mapping>
<union-subclass name="com.product.Folder" table="cxml_folder" extends="com.product.FolderContainedEntity">
<set name="children" inverse="true" cascade="all" subselect="false">
<key column="parentFolderId"/>
<one-to-many class="com.product.FolderContainedEntity"/>
</set>
</union-subclass>
</hibernate-mapping>
Code:
<hibernate-mapping>
<union-subclass name="com.product.FolderContainedEntity" abstract="true">
<many-to-one
name="parentFolder"
column="parentFolderId"
/>
</union-subclass>
</hibernate-mapping>
Code:
<hibernate-mapping>
<union-subclass name="com.product.Page" table="cxml_page" extends="com.product.FolderContainedEntity">
</union-subclass>
</hibernate-mapping>
Code:
<hibernate-mapping>
<union-subclass name="com.product.File" table="cxml_file" extends="com.product.FolderContainedEntity">
</union-subclass>
</hibernate-mapping>
Name and version of the database you are using: MySQL 5.0 Basically, I have an object hierarchy with 3 types of leaf node objects that are all 'folder-contained' (page, files, folders). There is a collection mapped in the Folder object that allows retrieval of all children using the parentFolderId foreign key in each table.
In my database there is table index on parentFolderId in all three tables as it is used frequently in where clauses.
The problem is that the union query hibernate generates for for retreiving all of these objects for the "children" collection relies on an inner select and thus can't leverage the parentFolderId index and therefore takes a long time on large datasets.
The query generated is this:
Code:
select children0_.parentFolderId as parentFo1_1_, children0_.id as id1_, children0_.id as id8_0_, children0_.name as name9_0_, children0_.clazz_ as clazz_0_ from
(
select parentFolderId, id, name, 45 as clazz_ from cxml_folder
union
select parentFolderId, id, name 46 as clazz_ from cxml_page
union
select parentFolderId, id, name 47 as clazz_ from cxml_file
)
children0_ where children0_.parentFolderId=?
My guess is that if the where clause for the parentFolderId were inside each query in the union it would be able to utilize the table index.
I confirmed that the index was not being used with the 'explain [QUERY]' statement in MySQL. Likewise, when I executed 'explain [QUERY]' on each of the individual queries in the union, I saw the usage of the parentFolderId index.
Is there any way to instruct hibernate to include this where clause in each of the queries in the union?
Login
FAQ
Search