CODEWITHSUNDEEP
javajpaplayframework
sanity
sanity

Reputation: 35782

JPA: Indexing by another field of a table referenced by a foreign key

I'm using JPA through the Play Framework, with Mysql.

Let's say I have a JQL query as follows:

SELECT p FROM Person p WHERE p.address.city = 'New York'

The address field is a foreign key reference to an Address table.

My understanding is that this query will require a table scan of the entire Person table, "dereferencing" the "address" field, and then doing a scan of the Address table too. Even if the city field on the Address object is indexed, we're still looking at a scan of the entire Person table.

I'd like to make this query fast by somehow creating an index on p.address.city in the Person table, is this possible in JPA/MySql?

Upvotes: 2

Views: 2217

Answers (1)

allenskd
allenskd

Reputation: 1805

I believe so, if you are using Hibernate you can use their annotation

 @org.hibernate.annotations.Table(
     name="table_name",
     indexes = { @Index(name="idx_", columnNames = { "xxx", "xx" } ) }
     )

or @Index annotated in your column (placed in model) from the org.hibernate.annotations package

I hope this helps

Upvotes: 2

Related Questions