MariaDB

Doubt about a select after creating a simple table in MariaDB 5.3.2

Asked by Santiago Lertora

I was just testing MariaDB 5.3.2 and i created a simple table:

MariaDB [santus]> create table names (id int unsigned not null auto_increment, name varchar(32) unique, primary key(id));

MariaDB [santus]> desc names;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

After this i just started to insert some records and find results where coming as if i done a sort by name asc

MariaDB [santus]> insert into names values ("", "santiago");
Query OK, 1 row affected, 1 warning (0.12 sec)

MariaDB [santus]> insert into names values ("", "Eugenia"), ("", "Maria");
Query OK, 2 rows affected, 2 warnings (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [santus]> select * from names;
+----+----------+
| id | name |
+----+----------+
| 2 | Eugenia |
| 3 | Maria |
| 1 | santiago |
+----+----------+
3 rows in set (0.00 sec)

MariaDB [santus]> insert into names values ("", "Max"), ("", "Diego"), ("", "Scott");
Query OK, 3 rows affected, 3 warnings (0.38 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [santus]> select * from names;
+----+----------+
| id | name |
+----+----------+
| 5 | Diego |
| 2 | Eugenia |
| 3 | Maria |
| 4 | Max |
| 1 | santiago |
| 6 | Scott |
+----+----------+
6 rows in set (0.00 sec)

MariaDB [santus]> select * from names order by id;
+----+----------+
| id | name |
+----+----------+
| 1 | santiago |
| 2 | Eugenia |
| 3 | Maria |
| 4 | Max |
| 5 | Diego |
| 6 | Scott |
+----+----------+
6 rows in set (0.00 sec)

So my normal selec * from names is tthe same as
MariaDB [santus]> select * from names order by name asc;
+----+----------+
| id | name |
+----+----------+
| 5 | Diego |
| 2 | Eugenia |
| 3 | Maria |
| 4 | Max |
| 1 | santiago |
| 6 | Scott |
+----+----------+
6 rows in set (0.00 sec)

Question information

Language:
English Edit question
Status:
Answered
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Sergei Golubchik (sergii) said : 		#1

If you do not specify ORDER BY, MariaDB is free to return rows in any order. Random, sorted ascending or descending, order of insertion, whatever.

But if you're particularly curious why the rows are returned sorted, it happens because
1. Your 'names' is an XtraDB table
2. Column 'name' is indexed
3. Column 'id' is a primary key
4. In XtraDB a primary key is implicitly a part of any other index, that is the index on 'name' is internally an index on (name, id)
5. Thus, this index is covering
6. In your query MariaDB uses a covering index to retrieve the rows - try "explain select * from names;" and you'll see "Using index" there
7. because rows are retrieved from the (name, id) index, you see them ordered

Revision history for this message
Philip Stoev (pstoev-askmonty) said : 		#2

Hello,

If you do not use an ORDER BY, the order of the returned records can be anything, including unordered and completely ordered by some column or key. It is not necessarily the order in which the records were inserted. Also, this ordering can vary between server versions and storage engines.

So, if you want to obtain a specific order, you must use ORDER BY. If you do use ORDER BY and do not get the order you desire, please let us know.

Revision history for this message
Sergey Petrunia (sergefp) said : 		#3

SQL language doesn't guarantee you any particular ordering if your SELECT statement is missing an ORDER BY clause. If you need the results to be ordered, you should use an ORDER BY clause.

In this particular case, the effect is caused by a recent optimization, that was done both in MySQL and MariaDB.

I get the same ordering-by-name as you:

MySQL [test7]> select * from names;
+----+----------+
| id | name |
 +----+----------+
| 2 | Eugenia |
| 3 | Maria |
| 1 | santiago |
 +----+----------+
3 rows in set (0.00 sec)

EXPLAIN shows why: it is using an index scan, not table scan:

MySQL [test7]> explain select * from names;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | names | index | NULL | name | 35 | NULL | 3 | Using index |
 +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

If we make the table not to have a covering index:

MySQL [test7]> alter table names add dummy int;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

We see the usual ordering:
MySQL [test7]> select * from names;
+----+----------+-------+
| id | name | dummy |
 +----+----------+-------+
| 1 | santiago | NULL |
| 2 | Eugenia | NULL |
| 3 | Maria | NULL |
 +----+----------+-------+
3 rows in set (0.00 sec)

MySQL [test7]> explain select * from names;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | names | ALL | NULL | NULL | NULL | NULL | 3 | |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Can you help with this problem?

Provide an answer of your own, or ask Santiago Lertora for more information if necessary.

To post a message you must log in.