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 enter image description here

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?

  • the order must be not important, but if necessary - copy InnoDB table without defined PK and disable auto increment for columns, export dump from MyISAM, Import into InnoDB. After all - alter table - add PK, set auto-increment for the proper value
    – a_vlad
    Mar 7, 2017 at 11:41
  • 1
    Why does the order matter? (Maybe we can solve the real problem instead of playing games with row ordering.)
    – Rick James
    Mar 9, 2017 at 22:19

1 Answer 1


@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.