Solved

MySQL Multi Column Primary Key

Posted on 2013-02-06
3
438 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
Comment Utility
Can you just add an "OrderBy" at the end of the statement?
0
 

Author Comment

by:markparr
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now