1

I have noticed that the following query

SELECT * FROM 'mytable' ORDER BY 'myfield'

produces a very differently ordered table to 

SELECT id FROM 'mytable' ORDER BY 'myfield'

The field that I am ordering by is a date field and for 50% of the records the value is null. Also, if in my SELECT clause I name each of the fields rather than rely on * it achieves the same order as the second query above.

Can anyone please advise why this is happening and what I can do to obtain consistent ordering?

Janis S.
  • 2,526
  • 22
  • 32
Mark Williams
  • 192
  • 2
  • 12

2 Answers2

3

Without a second level of ordering the null records will appear in any order the engine desires... Likely the order of the index on ID, or a composite index involving date. If you want consistent ordering order by the date field then the Primary key of the table. If no primary key exists, then you need to order by all the columns which would make a record unique.

So..

SELECT id FROM `mytable` ORDER BY `myfield`, `PK`

vs

SELECT * FROM `mytable` ORDER BY `myfield`, `PK`

Will result in the same order.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Ordering by *two* string literals isn't going to make much of a difference. – spencer7593 Apr 14 '16 at 23:20
  • @spencer7593 yes, I agree, but myfield and pk were "Placeholders" for the actual field names not intended to be literals. thus MyField and PK and MyTable since the syntax of this is INVALID if my table is in ticks. – xQbert Apr 15 '16 at 02:19
  • If the identifiers did contain single quotes, they could be escaped by enclosing them in backticks.... **`SELECT id FROM \`'mytable'\` ORDER BY \`'myfield'\`, \`'PK'\``**. – spencer7593 Apr 15 '16 at 02:44
  • I have added the primary key to the order by clause and that now returns the identical result. However, each of the queries I first posted returns the results in precisely the same order each time they are executed, just not the same order as each other. – Mark Williams Apr 16 '16 at 09:26
  • Extremely odd. in both cases you're hitting the same table, with an order by the same yet, row ordering is different.... I just don't see how that's possible. can you create an [SQLfiddle](http://www.sqlfiddle.com) that shows this behavior? Here's [my example](http://www.sqlfiddle.com/#!9/d56085/2/0) – xQbert Apr 16 '16 at 13:49
  • All I can think of is that * and ID use different indexes which cause the order to be different. – xQbert Apr 07 '17 at 12:43
0

It looks like you are ordering by a string literal. What's up with the single quotes around 'myfield'? With a string literal, the same exact value is going to be assigned to every row that is returned. And MySQL can return rows in any order it chooses. There isn't any order that would violate the ORDER BY 'string literal'. I wouldn't be surprised if the optimizer just discards a meaningless ORDER BY clause.

In MySQL, identifiers can be escaped by enclosing them in backticks. On my keyboards, that's the ` ~ key on the upper left, just left of the 1 ! key.

Single quotes enclose a string literal.

(... bypassing a discussion of double quotes and ANSI_QUOTES in sql_mode ...)

What does this query return?

SELECT 'myfield' FROM mytable LIMIT 50 ;

On my system, I get 50 copies of the same string. If I enclose the table name in single quotes, I get a syntax error.

The string literal works exactly the same way in the ORDER BY clause... the same string value appears on every row.

The difference in ordering you observe is likely due to a different access plan. The retrieval of one column may be using an index, the retrieval of all columns is probably a full scan of the table.

If your query isn't using single quotes, then please feel free to post the exact query that you are executing.

spencer7593
  • 106,611
  • 15
  • 112
  • 140