less than optimal LIMIT with JBossQL

[jurjanpaul]

Hello,

[After posting this message directly to the mailing list earlier, I got the impression that using the online forum is the preferred way. Sorry for the repetition.]

Using JBoss 3.2.6, our application queries a MySQL (4.0.23) database with a dynamic JBossQL query that contains a LIMIT clause.

However, in the SQL query that is generated by the container to be processed by MySQL, unfortunately no LIMIT clause appears, causing MySQL to do a lot more work (taking a lot more time) than is actually necessary (finding and sorting 100.000 records in stead of just the first 100). Unfortunately, it doesn't seem possible to further optimize at MySQL level (e.g. by adding even more indexes).

So, why does JBoss not actually preserve the LIMIT clause on the generated SQL query? Of course, I really wonder whether it is possible to add configuration to make this happen after all?


Thanks a lot!

Yours,
Jurjan-Paul Medema

[juha]

"jurjanpaul" wrote:

So, why does JBoss not actually preserve the LIMIT clause on the generated SQL query?

Use declared SQL or override the finder implementation yourself an inject the MySQL specific syntax.