0

I'm testing MySQL 5.6 and noticed some gap on my table idx.

while using two simple ways to bulk insert the same data on a indexed table, they produce two different indexes.

They are not weird structures just:

  1. normal insert using value()
  2. insert using select

Also, I'm not using especial insert condition, only simple insert and auto index.

The first, operate as expected but the second will generate gaps on the table index per each bulk insert.

Here is my script, to demonstrate this behavior: http://sqlfiddle.com/#!9/b138d/1

I'll be glad if someone can explain it or tell me if I'm doing something wrong.

Have a lovely celebration day..

Quijote Shin
  • 501
  • 4
  • 11
  • its on the fiddle http://sqlfiddle.com/#!9/b138d/1 I created two procedures to reproduce the effect, also there is a button to show plan – Quijote Shin Aug 27 '15 at 18:23
  • Related: [http://stackoverflow.com/questions/17668369/why-does-mysql-skip-some-auto-increment-ids](http://stackoverflow.com/questions/17668369/why-does-mysql-skip-some-auto-increment-ids): cerd's answer sums it up nicely. – concat Aug 27 '15 at 18:37
  • thank you @concat, but I set trx mode to uncommited and commited, but keep happening , and did you why only affect insert from select?? – Quijote Shin Aug 27 '15 at 19:20
  • Check: [14.5.5 AUTO_INCREMENT Handling in InnoDB](http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html). – wchiquito Aug 27 '15 at 21:03
  • Weird! It seems to increment by 2**N-1 for the next start. Your was 15. Shorter inserts had 7. – Rick James Aug 29 '15 at 04:11
  • exactly, that the documentation says it is normal and beneficial for the system, I think is a mere excuse. this does not happen in mssql :c – Quijote Shin Aug 29 '15 at 04:36
  • Have you tried to set `innodb_autoinc_lock_mode variable = 0`?. With other types of engines (e.g.: `MyISAM`), the situation does not occur. – wchiquito Aug 29 '15 at 09:40

0 Answers0