Multi-column Primary Key
Posted by:
Mark Parr
Date: February 06, 2013 03:18PM
Running under a MySQL 5.0.45 server, I have created a MyISAM Table containing the columns:
user :: varchar(8)
datetime :: datetime
seqno :: int(11) -- autoincrement
subject :: varchar(20)
text :: text
and have set my Primary Key as multi columns using (user, datetime, seqno).
I did a few INSERTS and all was looking ok but then I had a new INSERT of data appear out of order on the next SELECT. A few more INSERTS worked as expected w/ newer entries appearing at the end of SELECTED data but then another entry again jumped ahead of the older entries.
A current example portion of my data for columns (user, datetime, seqno, subject, text) gets ordered as follows when returning data from a SELECT:
USER1, 2013-02-06 02:24:17, 19, subject 19, text 19
USER1, 2013-02-06 02:24:55, 22, subject 22, text 22
USER1, 2013-02-06 02:24:30, 20, subject 20, text 20
USER1, 2013-02-06 02:24:42, 21, subject 21, text 21
Any ideas why Entry #22 would get placed between 19 and 20 in the data?
Thanks