Search : MySQL Primary Key Composed - no use index

G. S. · **Joined:** Fri Feb 05, 2010 10:54 am **Posts:** 28

I have a problem with MySQL.
Request with In Clause and primary key composed have a bug.
MySQL couldn't use existing index when request :

Code:

Select *
From xxx
where (y1,y2,y3) in ((v11,v12,v13),(v21,v22,v23),(v31,v32,v33))

y1,y2,y3 is composed primary key.

when i request :

Code:

Select *
From xxx
where (y1,y2,y3) in ((v11,v12,v13))

it's ok same as :

Code:

Select *
From xxx
where (y1,y2,y3) = (v11,v12,v13)
     or (y1,y2,y3) = (v21,v22,v23)
     or (y1,y2,y3) = (v31,v32,v33)

in this 2 last cases, MySQL succeeded to use existing index.

So : Hibernate Search use in clause after find result in lucene index..
Have you encounter this problem before ?

Have you any solutions ?

G. S.

amin-mc · **Joined:** Wed Oct 03, 2007 2:31 pm **Posts:** 205

Hi

I'm slightly struggling to understand the problem that you are describing. It seems as though you are having a problem with MySQL and it doesnt have anything to do with Hibernate Search. Are you having a hibernate core issue? Can you provide more information about the problem.

Thanks

G. S. · **Joined:** Fri Feb 05, 2010 10:54 am **Posts:** 28

Sorry I'm french ! lol

Hibernate Search use IN clause to select entity :

Code:

Hibernate: select this_ (.....xx....) from Demande this_ where ((this_.RDES, this_.TYPE_DEM, this_.UI_PHAR) in ((?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)))

But MySql have a problem with IN clause...
It doesn't use index of primary key or other index on table to make the select...
that is the reason of sluggishness of select.

Yes it's a MySql problem ! but could we bypass this bug of MySQL in Hibernate Search usage ?

Thanks

G. S.

amin-mc · **Joined:** Wed Oct 03, 2007 2:31 pm **Posts:** 205

This feels as though it's more of a question for the HIbernate core forum, but I could be wrong. Hibernate Search utilises apache lucene search engine to perform a free text search on your domain model and then gets back a reference to the entities in your db.

G. S. · **Joined:** Fri Feb 05, 2010 10:54 am **Posts:** 28

Yes, it could.
I don't know exactly.

sanne.grinovero · **Posted:** Fri Mar 26, 2010 1:02 pm

while I'm disappointed about MySQL, I agree it should be reasonable to create a workaround.
Could you open a JIRA request ? Care to provide a patch?

G. S. · **Joined:** Fri Feb 05, 2010 10:54 am **Posts:** 28

For JIRA, i haven't it.
I'll see this on monday.

I see just one solution to workaround.
It's ok for Oracle and PostgreSQL, i tested it.

To workaround, we have to make select with OR in where clause :

Code:

select .....
From .....
where (x1,x2,x3) = (v11,v12,v13)
OR (x1,x2,x3) = (v21,v22,v23)
OR (x1,x2,x3) = (v31,v32,v33)
...

G. S.

sanne.grinovero · **Posted:** Fri Mar 26, 2010 2:27 pm

Quote:

For JIRA, i haven't it.

this is where we track bugs and feature requests:
http://opensource.atlassian.com/project ... se/HSEARCH

you should post the proposal there, so that it gets attention from all developers and gets assigned to someone. Otherwise I'll forget it ;-)

Quote:

I'll see this on monday.

no problem.

btw, thanks for spotting this, I wouldn't ever have noticed.

G. S. · **Joined:** Fri Feb 05, 2010 10:54 am **Posts:** 28

Ok ;) Thanks.
Have a good weekend !

G. S. · **Joined:** Fri Feb 05, 2010 10:54 am **Posts:** 28

New JIRA Request : http://opensource.atlassian.com/project ... SEARCH-478