[hibernate-issues] [Hibernate-JIRA] Created: (HSEARCH-478) Hibernate Search : MySQL Primary Key Composed -> No use DB index on select

[Gaël Saunier (JIRA)]

Hibernate Search : MySQL Primary Key Composed -> No use DB index on select
--------------------------------------------------------------------------

                 Key: HSEARCH-478
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HSEARCH-478
             Project: Hibernate Search
          Issue Type: Bug
          Components: optimizer
    Affects Versions: 3.1.0.GA
         Environment: 

Eclipse SDK - Version: 3.3.2
MySql - 5.1.44-community
Hibernate Search - 3.1.0.GA
hibernate-entitymanager - 3.4.0.GA
hibernate-annotations - 3.4.0.GA
hibernate-core - 3.3.2.GA
Show »
Eclipse SDK - Version: 3.3.2 
MySql - 5.1.44-community 
Hibernate Search - 3.1.0.GA 
hibernate-entitymanager - 3.4.0.GA 
hibernate-annotations - 3.4.0.GA 
hibernate-core - 3.3.2.GA
            Reporter: Gaël Saunier


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

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 :

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

it's ok same as :

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

Hibernate Search use IN clause to select entity :

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

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 :

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


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira