Solved

MySQL Multi Column Primary Key

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP: concatenate query 13 79
change database name 2 36
MySQL Error Code 2 25
Optimize the query 5 43
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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