How data is ordered in MySql by default

Question

If I have 1000 rows in a MySQL database, I know that using TOP or LIMIT makes the query faster, but I want to know how the default data is ordered - database?

And how to make order by time or username by default so that using TOP or LIMIT is quick and easy?

Answer 1

In InnoDB, strings are stored in primary key order. If you use LIMIT without ORDER BY , you will always get the rows with the smallest primary key values, even if you inserted them in random order.

 create table foo (id int primary key, x char(1), y int) engine=InnoDB; insert into foo values (5, 'A', 123); insert into foo values (9, 'B', 234); insert into foo values (2, 'C', 345); insert into foo values (4, 'D', 456); insert into foo values (1, 'E', 567); select * from foo; +----+------+------+ | id | x | y | +----+------+------+ | 1 | E | 567 | | 2 | C | 345 | | 4 | D | 456 | | 5 | A | 123 | | 9 | B | 234 | +----+------+------+ 

In MyISAM, strings are saved wherever they fit. Initially, this means that lines are added to the data file, but as you delete lines and insert new ones, spaces left by deleted lines will be reused by new lines.

 create table bar (id int primary key, x char(1), y int) engine=MyISAM; insert into bar values (1, 'A', 123); insert into bar values (2, 'B', 234); insert into bar values (3, 'C', 345); insert into bar values (4, 'D', 456); insert into bar values (5, 'E', 567); select * from bar; +----+------+------+ | id | x | y | +----+------+------+ | 1 | A | 123 | | 2 | B | 234 | | 3 | C | 345 | | 4 | D | 456 | | 5 | E | 567 | +----+------+------+ delete from bar where id between 3 and 4; insert into bar values (6, 'F', 678); insert into bar values (7, 'G', 789); insert into bar values (8, 'H', 890); select * from bar; +----+------+------+ | id | x | y | +----+------+------+ | 1 | A | 123 | | 2 | B | 234 | | 7 | G | 789 | <-- new row fills gap | 6 | F | 678 | <-- new row fills gap | 5 | E | 567 | | 8 | H | 890 | <-- new row appends at end +----+------+------+ 

Another exception, if you use InnoDB, is that you retrieve rows from the secondary index instead of the primary index. This happens when you see the "Using Index" note on the output of EXPLAIN.

 alter table foo add index (x); select id, x from foo; +----+------+ | id | x | +----+------+ | 5 | A | | 9 | B | | 2 | C | | 4 | D | | 1 | E | +----+------+ 

If you have more complex queries with joins, this becomes even more complicated because you get the rows returned by the default order of the first available table (where the “first” depends on the optimizer choosing the order of the tables), then the rows from the joined table will depend on row order from the previous table.

 select straight_join foo.*, bar.* from bar join foo on bar.x=foo.x; +----+------+------+----+------+------+ | id | x | y | id | x | y | +----+------+------+----+------+------+ | 1 | E | 567 | 5 | E | 567 | | 5 | A | 123 | 1 | A | 123 | | 9 | B | 234 | 2 | B | 234 | +----+------+------+----+------+------+ select straight_join foo.*, bar.* from foo join bar on bar.x=foo.x; +----+------+------+----+------+------+ | id | x | y | id | x | y | +----+------+------+----+------+------+ | 5 | A | 123 | 1 | A | 123 | | 9 | B | 234 | 2 | B | 234 | | 1 | E | 567 | 5 | E | 567 | +----+------+------+----+------+------+ 

The bottom line is that it's best to be explicit: when you use LIMIT , specify ORDER BY .

Answer 2

How to order by time or default username to use TOP or LIMIT easily and quickly?

When using InnoDB as a mechanism, you can set one index for it (column or collection). If these values ​​are unique, they will be ordered as such when saved.

You can use regular indexes to speed up sorting if they are used as part of the query criteria, or if the query receives data from all rows. They do not help if your criteria use other indexes than the ones you sort.

Example

Given the following table:

 | username (Primary) | time (Index) | country | 

These queries will have sortings that use the index:

 select * from users order by time #Will use the index select * from users where time between X and Y order by time #Will also use the index 

It will not (in most cases ...) use the index to sort:

 select * from users where country = China order by time 

Answer 3

Usually it appears in the order in which it is inserted, but you cannot rely on it. If you need a specific order, always indicate it.

Add indexes to "time" and "username" to make it faster, and select only the data you need.

Answer 4

the default order is unspecified (ie unreliable)

add the ORDER BY clause to the selection to be sure.