Mysql:indexes and order of rows in a table

Question

Step 1:

I am creating a simple table.

CREATE TABLE `indexs`.`table_one` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

Step 2:

I make two inserts into this table.

insert into table_one (name) values ("B");
insert into table_one (name) values ("A");

Step 3:

I make a select, I get a table, the records in which are ordered by id.

SELECT * FROM table_one;

This is the expected result, because in mysql the primary key is a clustered index, therefore the data will be physically ordered by it.

Now the part I don't understand.

Step 4:

I am creating an index on the name column.

CREATE INDEX index_name ON table_one(name)

I repeat step 3 again, but I get a different result. The lines are now ordered according to the name column.

Why is this happening? why the order of the rows in the table changes in accordance with the new index on the name column, because as far as I understand, in mysql, the primary key is the only clustered index, and all indexes created additionally are secondary.

Answer 1

I make a select, I get a table, the records in which are ordered by id. [...] This is the expected result, because in mysql the primary key is a clustered index, therefore the data will be physically ordered by it.

There is some misunderstanding of a concept here.

Table rows have no inherent ordering: they represent unordered set of rows. While the clustered index enforces a physical ordering of data in storage, it does not guarantee the order in which rows are returned by a select query.

If you want the results of the query to be ordered, then use an order by clause. Without such clause, the ordering or the rows is undefined: the database is free to return results in whichever order it likes, and results are not guaranteed to be consistent over consecutive executions of the same query.

select * from table_one order by id;
select * from table_one order by name;