0

I don't understant, why MySQL queries completely random, and unsorted rows, when I'm trying to get only 'id' from table. I won't to use 'ORDER by ...'.

SELECT `id` FROM `indicator` LIMIT 20;

This query returns completely random rows, and unsorted.

If I'll add name, to my query, and code will look like:

SELECT `id`, `name` FROM `indicator` LIMIT 20

I will get right sorted elements, which I need. So, what is that?

Mully
  • 233
  • 1
  • 10

2 Answers2

2

If you're not specifying an ORDER BY, MySQL is free to get rows from anywhere it likes, and it will choose the most efficient option. This might be blocks that are in memory or just the start of the table, etc.

When you add the 'name' column, the most efficient option is almost certainly using an index that exists for the 'name' column. Since indexes are always ordered, you get sorted output. If the index were descending, you'd get the last 20 rows, not the first.

You can add an index on the 'id' column (why is it not already there?), but that still doesn't guarantee correct sorting, so it's best to use ORDER BY.

Cyrus
  • 2,135
  • 2
  • 11
  • 14
1

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

So, to answer your question:

  • MySQL sorts the records however it wants without any guarantee of consistency.
  • If you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.
Tanvir
  • 126
  • 6