I have three persistent objects, user, site, and health. A user may have multiple sites. A site is monitored by a health check and the health status is recorded in HEALTH table (health).

My mapping is as follows,

User.hbm.xml:

...
<set
name="sites"
lazy="false"
inverse="true"
cascade="delete"
sort="unsorted"
order-by="NAME asc"
>

<key
column="USER"
/>

<one-to-many
class="com.example.model.Site"
/>
</set>
...

Site.hbm.xml:
...
<set
name="health"
lazy="false"
inverse="false"
cascade="delete"
sort="unsorted"
order-by="TIME_OF_LAST_UPDATE desc"
where="TIME_OF_LAST_UPDATE=(select max(h.TIME_OF_LAST_UPDATE) from HEALTH h where h.SITE=SITE)"
>

<key
column="SITE"
/>

<one-to-many
class="com.example.model.Health"
/>
</set>
...

When site is initialized, I only need to load the latest health. I do not use lazy loading as there are no more than five sites per user. This works great when the HEALTH table is small (<100 rows). But when the table grows bigger, e.g. larger than 2000 rows, it could take more than 20 seconds to find a user. My database is MySQL 4.1.2 running at RedHat 8. At the time of the query, the CPU usuage of mysqld peaked at 100%.

I am not sure where the problem is. Am I doing something wrong? Or does MySQL not like the subquery?

Thanks.
Alan

A subselect is no more expensive than a join.

Are you sure you have appropriate indexes defined?

Did you check the query plan?

Well, it appears that the table is indexed and the query looked ok to me. I am really stumped.

Thanks.
Alan

mysql> show index from HEALTH;
+--------+------------+-------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| HEALTH | 0 | PRIMARY | 1 | ID | A | 1989 | NULL | NULL | | BTREE | |
| HEALTH | 1 | SITE | 1 | SITE | A | 2 | NULL | NULL | | BTREE | |
| HEALTH | 1 | Update_Time | 1 | TIME_OF_LAST_UPDATE | A | 1989 | NULL | NULL | | BTREE | |
+--------+------------+-------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+



Hibernate: select user0_.ID as ID, user0_.TIME_OF_LAST_UPDATE as TIME_OF_2_, user0_.CITY as CITY, user0_.STATE as STATE, user0_.STREET_ADDRESS as STREET_A5_, user0_.ZIP as ZIP, user0_.EMAIL_ADDRESS as EMAIL_AD7_, user0_.FIRST_NAME as FIRST_NAME, user0_.INITIAL as INITIAL, user0_.LAST_NAME as LAST_NAME, user0_.PASSWORD as PASSWORD, user0_.ROLE as ROLE, user0_.STATUS as STATUS, user0_.TELEPHONE as TELEPHONE, user0_.TIME_OF_CREATION as TIME_OF15_, user0_.TIME_OF_LAST_LOGIN as TIME_OF16_, user0_.USERNAME as USERNAME from USER user0_ where (user0_.USERNAME=? )

Hibernate: select sites0_.ID as ID__, sites0_.USER as USER__, sites0_.ID as ID0_, sites0_.TIME_OF_LAST_UPDATE as TIME_OF_2_0_, sites0_.ADDRESS as ADDRESS0_, sites0_.DESCRIPTION as DESCRIPT4_0_, sites0_.LEVEL as LEVEL0_, sites0_.MONITORED as MONITORED0_, sites0_.NAME as NAME0_, sites0_.PORT as PORT0_, sites0_.SERIAL_NUMBER as SERIAL_N9_0_, sites0_.USER as USER0_ from SITE sites0_ where sites0_.USER=? order by sites0_.NAME asc

Hibernate: select health0_.ID as ID__, health0_.SITE as SITE__, health0_.ID as ID0_, health0_.TIME_OF_LAST_UPDATE as TIME_OF_2_0_, health0_.SITE as SITE0_, health0_.STATUS as STATUS0_ from HEALTH health0_ where health0_.SITE=? and health0_.TIME_OF_LAST_UPDATE=(select max(h.TIME_OF_LAST_UPDATE) from HEALTH h where h.SITE=health0_.SITE) order by health0_.TIME_OF_LAST_UPDATE desc