Mysql Question

[bg370]

I'm posting in the PHP forum cause that's what I'm using to connect to MySQL. If there's a better place to post this let me know...

Anyway, I have data that consists of discrete lines of text which must be in a certain order. Like if you were taking a block of computer code with line numbers and storing each line as a row in a table.

That's fine, but when when it comes time to delete a line, we have a gap in the line numbers. So I have to go to each line after the deletion and decrement it's line number so that it's all in continuous order again.

I'm fine with doing that and obviously the code is pretty straightforward, but I wonder if this is the most efficient way. Is there another way of, say, structuring the data that would reduce the load on the sql server? Honestly, there aren't a ton of lines in each data set - maybe a few hundred. So it's possible that the correct answer is "don't worry about it". Just wondering.

[flagbarton]

are you using php? try to use a counter in fetching the data then echo the counter, bcoz if your using the ID(autoincrement) of mysql everytime you delete you also delete(part of deletion) that number. and if not, just in mysql idont know either =p

[bg370]

I'm using a separate line number field to keep track of the order.

[Ezzaral]

As long as the order of the lines does not change, why should it matter if there are gaps in the sequence? You can still sort them correctly with the gaps and generate the displayed line numbers yourself in the output as flagbarton indicated.

[bg370]

Yea I guess that's true, thanks. But now I'm realizing that inserts into the middle of it will screw up the order anyway. I may just need to reorder with each insert/delete unless there's another idea.

[Ezzaral]

Well, you could get by with renumbering on insert alone, but go with whichever you prefer.