1

I keep some articles in MySQL database as follows:

id      subject         body        order_no
============================================
1       subject 1       body 1      1
2       subject 2       body 2      2
3       subject 3       body 3      3
4       subject 4       body 4      4
5       subject 5       body 5      5

articles are adding to the to the table with their order number. Every time a new record is added to the table the other records should be shifted down if the new record has an order number which has already existed in database. For example we have a new record as follows:

id      subject         body        order_no
============================================
6       subject 6       body 6      3

in this case we are supposed to get the following outcome:

id      subject         body        order_no
============================================
1       subject 1       body 1      1
2       subject 2       body 2      2
3       subject 3       body 3      4
4       subject 4       body 4      5
5       subject 5       body 5      6
6       subject 6       body 6      3

it must be considered that records can be deleted or updated at any time while it shouldn't affect on records ordering and output must be shown as above (there shouldn't be any gap between "order_no"s).

What is the best solution to implement it by php and mysql? and how should I change my tables structure to get the best performance while the number of records are increased by time.

Hamid Ghorashi
  • 1,003
  • 3
  • 14
  • 29

1 Answers1

0

You should let mysql decide how to store the information and sort it:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, subject, body, order_no

Hope that helps.

hd1
  • 33,938
  • 5
  • 80
  • 91
  • yes but this is just for reading from database. how can I update a large number of records while inserting a new one!? It increases the cost of database and affects performance. I am gonna restructure my design in a way that it improve database performance. – Hamid Ghorashi Apr 29 '14 at 06:44
  • Get the basics working first, then focus on optimisation **as you need it** – hd1 Apr 29 '14 at 07:08