CODEWITHSUNDEEP
mysqlsql-order-by
pzogr
pzogr

Reputation: 424

mysql database insert row between records

I need to insert rows of data in a specific order. Sometimes I forget to insert the row on time and I have to insert it later. Other rows have taken up its place though and till now I manually (programmatically of course) change the index of different number of rows - it could be a couple of rows or hundreds of rows. This is not very efficient and I was looking for another way to go. My thought was to order by date and create a "day's index" number to reorder only the day's records but I was wandering... is there any mysql way to reorder the rows? That is to inform mysql about the required row position and then let it update the primary keys?

Upvotes: 1

Views: 1080

Answers (2)

user1864610
user1864610

Reputation:

I think you need to look at your table design. This is actually a non-problem for most applications because it would have been addressed at the start.

Now, you need to add a DateTime column to your table, and initialise it with some sensible values for the data that's already there. As new rows are added, set the DateTime column in each new row to the actual DateTime. If you have to add a row late, set the DateTime to the time the record should have been added.

When you query your table, use ORDER BY myDateTime (or whatever you decide to call it). Your rows should appear in the correct order.

For small tables (less than a few thousand rows) an index might not help much. For larger tables you should index your DateTime column. You'd have to run some tests to see what works best.

Upvotes: 1

Sumit Gupta
Sumit Gupta

Reputation: 2192

What you think is actually the solution. Create a Date column if not already, and then Create Index on that field, also use Order by in your Query. There is no way other than manual, and even if there is it is not recommended to play with MYSQL way of storing rows, because row storage is done by DB Engine and it is not ideal to play with them, as they store row in best optimal way, so why mess their efficiency for such a small thing.

Upvotes: 1

Related Questions