Change of order of rows in table

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I am using MySQL 5.1.15-beta

I have a table viz PLANTF.

Field | Type | Null | Key | Default | Extra |
±-------------------±----------------------±-----±----± ------------------±------+
| PLANID | smallint(5) unsigned | NO | PRI | 0 | |
| PLANNAME | varchar(10) | NO | | | |
| DESCRIPTION | mediumtext | YES | | NULL | |
| INCREMENTAL | tinyint(3) unsigned | NO | | 0 | |
| SUBSEQUENT | tinyint(3) unsigned | NO | | 0 | |
| NRC | decimal(7,2) unsigned | NO | | 0.00 | |
| PORTNRC | decimal(7,2) unsigned | NO | | 0.00 | |
| MRC | decimal(7,2) unsigned | NO | | 0.00 | |
| PORTMRC | decimal(7,2) unsigned | NO | | 0.00 | |
| CONTRACTPERIOD | smallint(5) unsigned | NO | | 0 | |
| EARLYCANCELCHARGES | decimal(7,2) unsigned | NO | | 0.00 | |
| CREATED | timestamp | NO | | CURRENT_TIMESTAMP | |
| CREATEDBY | varchar(25) | NO | | | |
| MODIFIED | datetime | NO | | | |
| MODIFIEDBY | varchar(25) | NO | | | |
±-------------------±----------------------±-----±----± ------------------±------+
Now when the table was having ENGINE as INNODB the table looked as follows:

SELECT * FROM PLANTF;

±-------±---------±-------------------±------------±— --------±-----±--------±------±--------±--------------- ±-------------------±--------------------±----------±— -----------------±-----------+
| PLANID | PLANNAME | DESCRIPTION | INCREMENTAL | SUBSEQUENT | NRC | PORTNRC | MRC | PORTMRC | CONTRACTPERIOD | EARLYCANCELCHARGES | CREATED | CREATEDBY | MODIFIED | MODIFIEDBY |
±-------±---------±-------------------±------------±— --------±-----±--------±------±--------±--------------- ±-------------------±--------------------±----------±— -----------------±-----------+
| 0 | default | default plan | 6 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
| 1 | tulip | 2 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
| 2 | alfa | 5 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
| 3 | aims | 7 cents per minute | 30 | 6 | 0.00 | 0.00 | 60.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |

Now when i changed the engine to NDB the table looked like follows:

SELECT * FROM PLANTF

±-------±---------±-------------------±------------±— --------±-----±--------±------±--------±--------------- ±-------------------±--------------------±----------±— -----------------±-----------+
| PLANID | PLANNAME | DESCRIPTION | INCREMENTAL | SUBSEQUENT | NRC | PORTNRC | MRC | PORTMRC | CONTRACTPERIOD | EARLYCANCELCHARGES | CREATED | CREATEDBY | MODIFIED | MODIFIEDBY |
±-------±---------±-------------------±------------±— --------±-----±--------±------±--------±--------------- ±-------------------±--------------------±----------±— -----------------±-----------+
| 1 | tulip | 2 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
| 2 | alfa | 5 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
| 0 | default | default plan | 6 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
| 3 | aims | 7 cents per minute | 30 | 6 | 0.00 | 0.00 | 60.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
±-------±---------±-------------------±------------±— --------±-----±--------±------±--------±--------------- ±-------------------±--------------------±----------±— -----------------±-----------+

Any reasons plz why the order changed with same query.

Also i want to let u know that the same happened with other tables in my database which are having a column with primary key.

Plz help
Thanks in advance

2

Never relay on order of rows in the table - it is generally undefined unless you use ORDER BY in your Select