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

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.0.0.GA, 3.0.1.GA, 3.1.0.Beta1, 3.1.0.Beta2, 3.1.0.CR1, 3.1.0.GA, 3.1.1.GA, 3.2.0.Beta1 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 Fix For: 3.2.0.CR1 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) ...