Solved

MySQL Multi Column Primary Key

Posted on 2013-02-06
3
448 Views
Last Modified: 2013-02-07
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
0
Comment
Question by:markparr
3 Comments
 
LVL 7

Assisted Solution

by:Steve
Steve earned 50 total points
ID: 38861623
Can you just add an "OrderBy" at the end of the statement?
0
 

Author Comment

by:markparr
ID: 38861893
I've always been under the impression that the SELECT by default uses the Primary Key.  There is a Primary Key defined named PRIMARY but I can't seem to find the right syntax to make use of it nor can I seem to be able to rename it.

If I do an

ORDER BY user, datetime, seqno

on my SELECT statement I get the data returned in the order expected.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 50 total points
ID: 38863105
Hi markparr,

I've always been under the impression that the SELECT by default uses the Primary Key.  

Which is the wrong impression.  Primary keys are meant to define the unique identifier of a row.  And like other indexes, it speeds up selects when searching by columns defined by the primary key.  But it does not define the default sorting when doing a SELECT.

When a row is inserted to a table it does not follow a certain order.  So when you retrieve rows they also won't follow any order, unless you tell MySQL to do sort them in a particular way.

The primary key though will enable retrieving them in order a lot quicker, assuming your order by clause follows the way you created your primary key.

There is a Primary Key defined named PRIMARY but I can't seem to find the right syntax to make use of it nor can I seem to be able to rename it.

In the simplest sense, the way to make use of any index is to specify the columns that are part of it in your SQL, in the filtering, ordering and/or grouping clauses.  Which is what you did below.  I don't think you can rename indexes - if you really need to you can drop then recreate them.

If I do an

ORDER BY user, datetime, seqno

on my SELECT statement I get the data returned in the order expected.

Yes, that's the way.  HOWEVER, I noticed that seqno is auto_increment, so it's already unique and ordered, assuming you let MySQL do the job of assigning it a value.  So you really only need to have seqno as your only column for your primary key, and you only need to do "ORDER BY seqno".  It is best to keep your primary key small, especially for innodb tables since other indexes will also refer to it.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question