Hi Phillipe, The database always decides what indexes to use, never OJB ! OJB knows nothing about indexes (the repository.xml entry is, or used to be, used to generate the SQL/DDL to create the tables in the database. It isn't used at runtime)
The SQL query that has been generated *doesn't* use the ZIP field in the where clause of the query, and hence the index is not used. I have never used the JDO API with OJB (or indeed any JDO implementation), but I would guess that the use of a parameterised query is stretching the capabilities of the combination of the sun reference implementation and the OJB plugin. Try something like this (rememver, this mighr be complete rubbish as I've never used JDO): Query query = persistenceManager.newQuery(Usazip.class, "zip=='10001'"); Collection collection = (Collection) query.execute(); I hope and imagine this will generate SELECT A0.IDZIP,A0.ZIP FROM ZIP A0 WHERE A0.ZIP = '10001' That would, in turn, use the database's index on the ZIP column. HTH a little, Charles. > -----Original Message----- > From: Philippe Guillard [mailto:[EMAIL PROTECTED] > Sent: 28 May 2005 13:57 > To: ojb-user@db.apache.org > Subject: JDO Query / indexed column > > > Hi all, > > I use OJB/ JDO API. I know i should forget here thinking the > relational > way, but i wonder if my DB indexes are used when i execute a > JDOQL query > on a field that is not a primary Key but just indexed field. > (I ask this > because in case of Primary Key i suppose people here would > suggest using > getObjectById()) > Adding the index in my DB doesn't change anything so i > suppose it is not > used, and wonder about querying a large table? > (Forgot to say : newbie question:-)) > > Here is my quick sample: Zip codes table with (stupid but for > example) > idzip as PK meaning nothing, and the zip code as a String field on > which i added index in databse : > CREATE INDEX testIndex ON ZIP(zip); > > JAVA CLASS > public class Zip implements Serializable { > private int idzip; > private String zip; > > public void setIdzip(int newIdzip) { > this.idzip = newIdzip; > } > public void setZip(String newZip) { > this.zip = newZip; > } > } > > REPOSITORY > <class-descriptor class="net.talkgroups.model.bean.Usazip" > table="USAZIP"> > <field-descriptor name="idzip" > column="idzip" jdbc-type="VARCHAR" primarykey="true"/> > <field-descriptor name="zip" column="zip" > jdbc-type="VARCHAR" indexed="true" access="readonly"/> > <!-- also tried this > <index-descriptor name= "testIndex" unique= "true"> > <index-column name= "zip"/> > </index-descriptor> > --> > </class-descriptor> > > JDO QUERY > Query query = persistenceManager.newQuery(Usazip.class, "zip==param"); > query.declareParameters("String param"); > Collection collection = (Collection) query.execute("10001"); > // 10001 is > zip code > > I get these logs from DB: > 050528 19:56:05 27 Query SELECT 1 > 27 Query SET autocommit=0 > 27 Query SELECT A0.IDZIP,A0.ZIP FROM ZIP A0 > 050528 19:56:15 27 Query commit > > It is slow in all cases (with or without index), and much faster with > SQL in my DB client. (table is US zip codes) > > Regards > > Phil > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > ___________________________________________________________ HPD Software Ltd. - Helping Business Finance Business Email terms and conditions: www.hpdsoftware.com/disclaimer --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
