I am devoloping an application to analyze an arbitrary data set (output from a simulation) provided to our organization in the form of fully populated MySQL (MyISAM) tables (the simulation is run by another organization, so we have no say in the schema). The data base that we were provided is comprised of many interrelated tables, with absolutely no constraints, indices, etc defined. Several of the tables are quite large (2000000+ rows), and as such we need to define an index into those tables. Each simulation run will provide a whole new set of database tables, so we would like to automate the index creation process, without requiring the user to run any external tools first.

The problem is that the SchemaUpdate tool does not seem to generate the DDL to add an index to a pre-existing table, it only adds the index if the table does not exist when SchemaUpdate is run.

My Environment:
Hibernate 2.1.2
MySQL 4.1.0a
MySQL Connector/J 3.0.10

The relavant portion of the mapping file:
...
<property
name="simTime"
type="java.lang.Long"
update="true"
insert="true"
>
<column
name="simulationtime"
index="idx_asm_simulationtime"
not-null="true"
/>
</property>
...

SchemaExport generates the following DDL for this mapping:

drop table if exists aircraftstatemessage
create table aircraftstatemessage (
flightid INTEGER not null,
sequenceid BIGINT not null,
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
altitude DOUBLE PRECISION,
heading DOUBLE PRECISION,
simulationtime BIGINT not null,
groundspeed DOUBLE PRECISION,
primary key (flightid, sequenceid)
)
create index idx_asm_simulationtime on aircraftstatemessage (simulationtime)

However, SchemaUpdate generates nothing.

Any Ideas?

-Dan