Convert table from MyISAM to InnoDB and keep MyISAM rows order I'm currently rewriting old project and I've got this strange scenario.

It's an online store, it has products with images. The images are stored in separate table. Here is example query. The part after the id- in the img column is the same for every row for pid.

I want to migrate this table to InnoDB, but keeping this order. InnoDB orders by id by default.

SELECT * FROM products_gallery WHEREpid= :productID

This is MyISAM


There is no particular order, probably they are stored in the way the are inserted, altrought if that was the case they should be ordered by id. I don't know that is really happening, but I think MyISAM has it's own order then no ordering is specified. I want to migrate this to InnoDB and keep this same order.

The only solution coming to my mind is to go trough product images before changing the storage engine and record the position rows are returned. After that I'll add new column to the table position and update it with the recorded position and sorting by position after migration.

So my question is there any easier solution?
@a_vlad has your answer... create a new table with the same structure without keys and autoincrement (it is faster to manage indexes in batch than one row at a time).  Then insert into the new table select * from the old.  After population, any indexes (including the pk) can be applied and add autoincrementation advanced to the next value you want to generate.