database - MySQL Indexing Keys Fields -

can explain me happen , best way create third table on next clausule.

should :

index (id),         index (id_producto),         index (id_usuario) 

be removed?

create table producto (     id int not null auto_increment,     precio decimal,     primary key(id) )   engine=innodb; create table usuario (     id int not null auto_increment,     nombre varchar(100) not null,     primary key (id) )   engine=innodb; create table ordenes_productos (     id int not null auto_increment,     id_producto int not null,     id_usuario int not null,     fecha_hora datetime not null,      primary key(id, id_producto, id_usuario),     index (id),     index (id_producto),     index (id_usuario),      foreign key (id_producto) references producto(id),     foreign key (id_usuario) references usuario(id) )   engine=innodb; 

thanks all.

id int not null auto_increment, primary key(id, id_producto, id_usuario), index (id), index (id_producto), index (id_usuario), foreign key (id_producto) references producto(id), foreign key (id_usuario) references usuario(id) 

analysis:

• an auto_increment "unique"
• a primary key is, definition, "unique"
• starting compound primary key unique not make sense.
• foreign keys need indexes. is, foreign key (id_usuario)... needs index (id_usuario).
• the where clause in selects, updates, , deletes should studied determine indexes needed. (in absence of showing those, assume there no critical ones.)

considering of above, recommend these indexes:

primary key(id), index (id_producto), index (id_usuario) 

but... there consideration. "many-to-many mapping" between usuario , producto? if so, pair (usario, producto) "unique"? if yes these, then:

create table ordenes_productos (     id_producto int not null,     id_usuario int not null,     fecha_hora datetime not null,      primary key(id_producto, id_usuario),  -- map 1 way     index      (id_usuario, id_producto),  -- map other way     foreign key (id_producto) references producto(id),     foreign key (id_usuario) references usuario(id) )   engine=innodb; 

more details on many-to-many;

on topic...

create table producto (     id int not null auto_increment,     precio decimal,     primary key(id) )   engine=innodb; 

are there other fields in table? 'normalizing' price? not idea normalize "continuous" value (date, number, float, etc). caveat: comment normalization , comment many-to-many incompatible.

usuario different (with respect normalization) in varchar not 'continuous' , varchar bigger.