Mysql Auto increment without index

Question

I believe this is not possible, but I ask here to be sure.

Here are my thoughts:

• I want to insert many rows and generate ids for them (using auto increment)
• It is known to be faster to 1) not index, do your inserts and add your index after inserts are done, than 2) creating the index first and do your inserts. This is because of the overhead of indexing during inserts

Since I am doing an insert...select, I cannot generate the ids from the application.

So, basically, what would be perfect would be to have an auto increment that does not need to be indexed.

I don't understand why mysql need an index to implement auto increment? cannot simply store the last inserted id and then increment it?

Any input is welcome!

Answer 1

You can generate an ID like this

insert into destination_table (col_id, col2, col3)
select @rank := @rank + 1, colX, colY
from source_table
cross join (select @rank := 0) r
order by some_column