hibernate and mysql partitioning

Question

My app uses spring+hibernate+mysql.

Some of my tables are growing very big and horizontal partitioning would really benefit me in solving related performance issues.

I took a look at hibernate shards but didn't like the fact that it is not on the maven repo and that it is still labeled "beta".

I'm considering using mysql partitioning feature and have some questions:

If I create partitions using mysql can I keep using my app as usual without any errors from hibernate?

I understand I cannot explicitly tell hibernate which mysql partition to use. But if I use hibernate filters to "suggest" what partition to aim to will mysql optimize the query and use the right partition?

Example:

Say i partition my table by a column type that can be 1,2,3,4 - each type goes to a different partition.

If i add a hibernate filter on this type column so the final query will be someting like:

Select blabla from mytable where stuff AND type=3 (the hibernate filter added the type = 3).

Will mysql pick this up and treat it the same as

Select blabla from mytable PARTITION (type3) where stuff

Answer 1

Hey Can we use here session.createsqlquery("Select blabla from mytable PARTITION (type3) where stuff "); to resolve the issue? Hibernate support native API after all.

Thanks Sanjeev Yadav

Answer 2

MySQL does all the dirty work related to finding which partition the data may be/will be stored. Whether the table is paritionned or not is fully transparent from the client point of view, the queries remain unchanged. There is no such thing as SELECT col FROM mytable PARTITION(p); with MySQL.

[edit] As pointed out by TJChambers, SELECT col FROM mytable PARTITION(p); does exist as of v5.6, and allows for explicit, manual partition pruning.

Answer 3

While @YaK is correct about the location of the data, the MYSQL SELECT statement in 5.6 DOES allow you to specify the Partition as @samz questioned, for performance reasons. See http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html.