Does using order by on a non-primary key column negate default primary key sorting?

Databases
1

Hello;

I am working on a mysql database table for baseball games. There are three games per day.

The game_id column below is the primary key. The game_date column is an index key.

The game_date is a timestamp for the the day of the game. It is for 12:00:01 (one second after midnight) on the day of the game.

If I do a select clause with no order by directive it sorts with the game_id with an ascending sort order:

SELECT game_date from GAMES

Results:

game_id----game_date (timestamp)

15-------------1331942401 (Mar 16, 2012)
16-------------1331942401 (Mar 16, 2012)
17-------------1331942401 (Mar 16, 2012)

19-------------1332115201 (Mar 18, 2012)
20-------------1332115201 (Mar 18, 2012)
21-------------1332115201 (Mar 18, 2012)

23-------------1332460801 (Mar 22, 2012)
24-------------1332460801 (Mar 22, 2012)
25-------------1332460801 (Mar 22, 2012)

27-------------1332979301 (Mar 28, 2012)
28-------------1332979301 (Mar 28, 2012)
29-------------1332979301 (Mar 28, 2012)

If I do a select query with an order by game_date, with game_date ascending, it sorts the the game dates properly. However, some of the game_id numbers sort in an ascending fashion while others sort in a descending fashion.

SELECT game_date from GAMES ORDER BY game_date ASC

Results:

game_id----game_date (timestamp)

17-------------1331942401 (Mar 16, 2012)
16-------------1331942401 (Mar 16, 2012)
15-------------1331942401 (Mar 16, 2012)

21-------------1332115201 (Mar 18, 2012)
20-------------1332115201 (Mar 18, 2012)
19-------------1332115201 (Mar 18, 2012)

23-------------1332460801 (Mar 22, 2012)
24-------------1332460801 (Mar 22, 2012)
25-------------1332460801 (Mar 22, 2012)

27-------------1332979301 (Mar 28, 2012)
28-------------1332979301 (Mar 28, 2012)
29-------------1332979301 (Mar 28, 2012)

Problem: I need each of the three games per day to sort in an ascending fashion.

There are a few solutions to the problem. I can put the game_id ascending in the order by directive. Or, I could make the timestamp for the second game 12:00:02 (two seconds after midnight) and for the third game 12:00:03 (three seconds after midnight).

My primary concern why the game_id column is not sorting in an ascending fashion, after I use an order by directive for the game_date column, like it was before. It seems like it should.

Question 1: Should the game_id column sort in an ascending fashion after I apply the order by directive to the game_date column?

MySQL sorts by default using the primary_key (I think) when a directive like order by is not used.

Question 2: Does sorting the game_date column in ascending fashion using the order by directive negate the sorting of the game_id primary key?

Thanks.

2

MySQL sorts by default using the primary_key (I think) when a directive like order by is not used.

No. If there is no order by clause, the data is not sorted at all. It it only the columns present in the order by clause that affects how the result is sorted.

3

swampBoogie;

Thanks for responding to my question.