MySQL Select

When I use the SELECT statement without sorting on a table with a PRIMARY KEY, does the result
automatically gets sorted to the asecending order to the field with the PRIMARY KEY???

Also does MySQL hold the information of the records in the manner that were inserted on a table that doesn't contain a PRIMARY KEY???

1) yes

2) probably


(you can check both of this very easy..)

1)OK.

2)Is this Yes Or No? I want to know whether when you perfrom a SELECT query on a table that doesn't contain a PRIMARY KEY, does it sort the records in the Ascending order in the manner it was inserted..

Better if you also can direct me to reference on this with your reply..

make a little table with 2 fields, insert 5 records with equivalent data in one of the records and the following in the other c, d, e, a, b and see how they are returned. I imagine with no key you get them back either fifo or lifo but you can test this as easy as anyone else can.

I have tested this.. and works OK.. I want to know whether its true in all cases.. Something like a theoritical explanation or whether its mentioned in the manual.. I Don't want to mess up with data

how do you want the data returned? If you want the data returned in the order it was submitted then ad an auto increment id field and make it a key. If you want the data sorted some other way, sort it in that manner. I would assume that if you tested it and it works one way it will probably continue to work that way but if this is critical stuff why not control it yourself?

Yep.. Its kinda mission critical stuff.. I was not aware of using AUTOINCREMENT id fields earlier.. Using them now though to great effect.. What I'm worried is the existing datbases I have.. I was using VARCHAR field types for the id (id being the PRIMARYKEY) and used to generate the id via the code and insert them.. id looks like
'CMK0014','CMK0015',....
As you said it works fine without ORDER BY sorting, probably its automatically sorted in the manner it was inserted.. To generate the new id via the code I used to get the last id and generate the new one.. So far so good I didn't have any problems.. I know what I'm doing now, using AUTOINCREMENT is the best way to do.. Any suggestions to kinda migrate my old method to the current one??

Thanks for both of you zombie and bakertrg..

yes, you could migrate the code by adding a new field int to the data base, running a for loop to insert a progressively incremented number into that field, change the field to autoincrement. This might work (i.e. the next field will automatically increment) by itself and it might not, I think it does but if it does not and the auto field starts from 1, there is a way to set the autoincrement counter, have to look in the manual for that.