Hi,

I need to create an indexed string column.

I don't find the mapping to use. Can you help me please ?



Thanks for your help !

J-L

Hi J-L

Indexes are created on the database to help speed up queries. Mostly you just create the index and allow the db to use it. You don't have to do anything else after creating it. Sometimes, when writing SQL SELECT statements, and can give the db a hint on what indexes to use, but the db's query optimisation engine is under no obligation to use the hint! (It also depends on the db whether you can do this.) On the whole however, indexes are a "set and forget" type performance optimisation done on the db.

Keep in mind that indexing a character or string based column is also not generally a good idea, as the indexes are very slow to build and use compared to a more discrete type, such as an Int.

AFAIK, indexes are not used through nHibernate. In each mapping file you do need to have a <id> element, which matches either the primary key or another candidate key. This is done as :



and of course you can modify the type if your key is a string. But, I would strongly recommend using a surrogate Int type key.

Phil

I agree with you but my problem is that I need to search a record (in this case, a medical patient) by the first letter of Lastname. My code is:



If I create an index on the Last_Name column, the query is very faster.
And the patients table can have 500 000 records or plus ...

So, I have no choice. :-(

I'll finally create the index with

CREATE [UNIQUE] INDEX

It will be more simple.

Thanks for your help ! :-)

If you are generating your schema from your mappings with hbm2ddl, you can specify indexes in your mapping using the 'index' attribute. There are other options for controlling ddl generation as well. See http://www.hibernate.org/hib_docs/nhibe ... etguide-s1