mysql: Insert new row between 2 existes rows

[MByte]

Hi

I want to know How mysql order the rows ? if there is no incremental filed like serial or employee id.

and how can i insert new row between 2 old rows ?

EXAMPLE 1: ( Date as D-M-Y format )

Name registration

John 1/1/2007
Mike 3/1/2007
Mark 7/1/2007

I want to insert these data before (Mark), so the table should be like this

Name registration

John 1/1/2007
Mike 3/2/2007
Paul 8/3/2007
Mark 7/5/2007

so the registration filed will be sorted when i use this sql ( without using any order)
SELECT * from registration

any ideas ?

[Shrike]

The logical order in which rows appear in the table should be irrelevant. You should append an ORDER BY clause to your query to get the results as you want them

SELECT * from registration ORDER BY Name
# Or
SELECT * from registration ORDER BY Registration

I'd forget trying to insert at a particular point, theres no simple way to do it.

[Piranha]

You can't insert it between like that simply because in the database everything is unsorted. You sort it when getting the results by using ORDER BY. Add ASC or DESC to select in what order it should be.

SELECT * FROM registration ORDER BY date ASC

By the way, since you have different dates in your two examples they don't make much sence. You simply can't order by the first set of dates and get the second ones. To get the help you need we need to know exactly what you are after, using sloppy examples will not help in that.

[MByte]

what about using UUID() function when inserting the data ?

[laserlight]

what about using UUID() function when inserting the data ?

Just ORDER BY when retrieving.

[severeheadache]

Previous posters are correct. The physical order of the table is irrelevant. But, to give you some information, MySQL on MyISAM tables will order the physical order of he rows by the Primary Key.

IF you want to be totally crazy, you can ALTER the table with an ORDER BY statement.

ALTER TABLE test ORDER BY MyField;

This will re-structure the table's physical order, re-ordering the rows by putting them in asc order. We have done this in our reporting application to speed up lookups for drop downs. It makes the query come back a lot faster if it does not have to sort. Plus you can do crazy things like unbuffered queries for even more speed.

Ok, craziness aside, make another column. Make it an INT(6) field called Sequence. Since you already know the order you want the rows to be in, you can simply insert them with the proper sequence number. Leave big gaps in the numbers so you can insert them where you want them like so:

Name		Date		Sequence
John  		01/1/2007	1000
Mike		03/2/2007	2000
Mark		07/5/2007	4000
Paul		08/3/2007	3000

Then just select your rows and order by sequence. Way easier. MySQL also highly recommends you NOT rely on the physical order of the table because the Query optimizer will sometimes jumble your data to return it faster in some weird order by group by or union scenarios. You should just get used to ordering EVERYTHING to get expected results.

Hope this helps,

Mike

[MByte]

as you know For ALTER TABLE, using ORDER BY expression could cause a server
crash.

is there any way to move the row or edit primary key ? i hope yes

[Piranha]

I have 2 questions for you:

1. Can you read?

2. Do you understand English?

If you answered no to one or both questions you are forgiven. If you answered yes to both questions you have to be stupid. Your question have been answered by several people with several explanations, all leading to the same conclusion. Please leave this board alone and let us help people that want help instead.

[MByte]

Sorry Piranha
actually yes, I can read and understood !

but we try go more advanced in tunning up mysql from internal side.

Thanks all

[laserlight]

but we try go more advanced in tunning up mysql from internal side.

If severeheadache's first suggestion causes you a severe headache, you could try something along the same lines, but with a temporary table. The second suggestion is also feasible, though in the long run you might still run out of sequence numbers (which could be primary keys) in a given range.

is there any way to move the row or edit primary key ? i hope yes

You can edit the primary keys, but generally there is no need to. There is no way to "move" the row - how the rows are returned cannot be guaranteed, except by the use of an ORDER BY.

That said, are you sure that this ORDER BY is the bottleneck in your script? An ORDER BY the date looks most easily maintained to me, so such optimisations as suggested by severeheadache is only warranted if you have evidence that this is the bottleneck. Remember, premature optimisation is the root of all evil.

[tomhath]

Is MByte a troll? He's acting like one.

[severeheadache]

Well, first off, I need to address your statement.

as you know For ALTER TABLE, using ORDER BY expression could cause a server
crash.

Um, nope. We have over 60 MySQL reporting servers with over 80 TeraBytes of data that we use for realtime reporting for one of my clients (most tables with millions if not tens of millions of rows). We maintain an uptime of 99 (except for maintenance weekends every quarter). We execute those ALTER statements daily and it has NEVER caused our MySQL servers to crash. Now, it might in some versions, we use 4.1 not 5+.

I am NOT recommending this solution for your situation. I merely presented it as information and labeled it "craziness". Just do the damn orderby.

Now, your question. Yes it is possible to change your primary key. You would need to perform an ALTER statement. The easiest is to:

ALTER TABLE test DROP PRIMARY KEY ,
     ADD PRIMARY KEY ( MyNewField );

I did not mention before, you need to know this. When you ALTER a table in MySQL it copies the entire contents of the table into a temporary table, with the change you are making and then removes the old instance. This can cause real problems if you do it on a table with millions of row. I usually tell people ALTERs should be avoided if possible when you exceed 1,000,000 rows (of course there are times there is no way around it). There is something magical that seems to happen to MySQL at that row count.

Oh, and yeah, quit acting like a troll, we are all here to help each other.

-Mike