1

I should do really complicated tables reorder.

****The main table looks like:****

id - some data
1 - ...
2 - ...
3 - ...
6 - ...
9 - ...
12 - ...

As you can see, there are "empty" IDs (no 4, 5 between 3 and 6 etc).

****Table should look like****

id - data
1 - ...
2 - ...
3 - ...
4 - ...
5 - ...
6 - ...
7 - ...
etc (without empty IDs)

Why do I want to make it: this is a server database, includes millions IDs. The server crashes when maintaining such big numbers. If I reorder IDs and there won't be any empty ones, the MAX ID in this table will be much lower and it will prevent server from crashing.

The most complicated thing - this table has foreign key references (atleast only ID references) in like 4-5 different tables.

My thought is - reorder main table by IDs without empty ones and create new temp table with (oldID, newID) structure and then replace oldID's with newID's in the other related tables.

Any ideas how the query would look like? It will probably take half an hour to execute that query but it's the least problem.

  • 1
    Even if you reorder the data and add the missing IDs(which I think is a bad idea) what will you do in 1 years time when you reach the same large ID number ? When you saying the database is crashing with a large number what exactly do you mean ? Is MySQL generating an error ? running out of memory, high cpu etc ? – armitage Jul 5 '15 at 17:18
  • It's game server written on C++ which is using this database. Due to specific game client and server features, it cannot handle such big ID numbers for many players. The table i'm talking about is responsible for NPCs that are spawned ingame, everyone has his unique ID but there are empty IDs. The amount of IDs will not expand further as we're done with spawning new NPCs, just want to reorder the table to have no empty IDs. – Icefloes Jul 5 '15 at 17:33
  • 3
    I think you're looking for a wrong solution to your problem. And you didn't say what is the error you're getting. – mustaccio Jul 5 '15 at 18:14
  • It's not proper to mess with AUTO_INCREMENT values. – Rick James Jul 6 '15 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.