Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL Multi Column Primary Key

Posted on 2013-02-06
3
Medium Priority
?
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Assisted Solution

by:Steve
Steve earned 200 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 200 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

671 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