CODEWITHSUNDEEP
mysql
blue ghhgdtt
blue ghhgdtt

Reputation: 921

How Data is ordered in MySql by Default

If i have 1000s of rows in MySQL database, i know using TOP or LIMIT makes query faster, but i want to know, how data is ordered is database by default?

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

Upvotes: 1

Views: 778

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562310

In InnoDB, rows are stored in primary key order. If you use LIMIT with no ORDER BY, you'll always get the rows with the lowest 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, rows are stored wherever they fit. Initially, this means rows are appended to the data file, but as you delete rows and insert new ones, the gaps left by deleted rows will be re-used by new rows.

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 case if you use InnoDB is if you are retrieving rows from a secondary index instead of from the primary index. This happens when you see the "Using index" note in the EXPLAIN output.

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, it gets even more complicated, because you'll get the rows returned by the default order of the first table accessed (where "first" is dependent on the optimizer choosing the order of tables), then rows from the joined table will be dependent on the order of rows 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 an ORDER BY.

Upvotes: 4

Jeff Ferland
Jeff Ferland

Reputation: 18292

How to make order by time or username by default so that using TOP or LIMIT is easy and faster?

When using InnoDB as the engine, you can set one index (column or collection there of) to be the primary key. If those values are unique, there will be ordered as such when stored.

You can use regular indexes to make sorting faster if they are used as part of the query criteria, or if the query takes data from all rows. They don't help if your criteria uses other indexes besides those of the fields you are sorting.

Example

Given the following table: 

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

These queries will have sorts 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

Thise will not (in most cases...) use the index for sorting:

select * from users where country = China order by time

Upvotes: 2

Randy
Randy

Reputation: 16677

the 'default' order is unspecified (i.e. unreliable)

add an ORDER BY clause to the select to be sure.

Upvotes: 0

mpen
mpen

Reputation: 282865

It usually comes out in the order it's inserted, but you can't rely on that. If you want a specific order, always specify it.

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

Upvotes: 0

Related Questions