union-subclass strategy performance issue

[Sentencia La Malice]

Hi!. This is a performance issue. 

Our application can be easily extended via user plugins with their own persistent classes that can even extend our system classes, so we can get to a very hierarchical schema. Something like this:

abstract class C

class C1 : C

class C2 : C

.

.

.

class C100 : C

Since the hierarchy can grow without any bussiness limit we don't use the table-per-subclass strategy because we would hit the max join count of 61 tables on MySQL. So we use the union-subclass strategy, in this way queries are union selected and the limit on unions count are much more far.

The problem is that when it comes the time for lazy loading a property of type C (the top abstract parent), NHibernate issues a multi union query in the way:

SELECT aliases FROM ( 

SELECT aliases FROM C1 

UNION SELECT aliases FROM C2 

UNION SELECT aliases FROM C3

.

.

.

UNION SELECT aliases FROM C100) Where Id=?

A very slow query indeed, but we are prepared to live with that. The thing is that this query could be very easily optimized if performed in the way:

SELECT aliases FROM 

(

SELECT aliases FROM C1 Where Id=?

UNION SELECT aliases FROM C2 Where Id=?

UNION SELECT aliases FROM C3 Where Id=?

.

.

.

UNION SELECT aliases FROM C100 Where Id=?) 

We tested and confirmed that second query is much more faster (up to 2000% faster in our case) than NHibernate native one and that MySQL internal optimizer does not optimize that kind of query at all. So que question is: is there any way we can add the WHERE clause into the subselects?.

Thanks in advance.