Solved

MySQL Multi Column Primary Key

Posted on 2013-02-06
3
443 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

19 Experts available now in Live!

Get 1:1 Help Now