[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HSEARCH-478) Hibernate Search : MySQL Primary Key Composed -> No use DB index on select
Gaël Saunier (JIRA)
noreply at atlassian.com
Fri Apr 2 09:52:31 EDT 2010
[ http://opensource.atlassian.com/projects/hibernate/browse/HSEARCH-478?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36096#action_36096 ]
Gaël Saunier edited comment on HSEARCH-478 at 4/2/10 8:52 AM:
--------------------------------------------------------------
I just tested it for PostgreSQL, Oracle.. and it's ok.
i don't know for others
I think solution what use OR clause will be correct for all DB
was (Author: g.s.):
I just tested it for PostgreSQL, Oracle.. i don't know for others
But i think solution what use OR clause will be correct for all DB
OR clause is less complex for DB than IN clause with multi-values
> 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)
> ...
--
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
More information about the hibernate-issues
mailing list