About the Index in MYSQL [closed]

Posted on

Question :

I have a question about indexes in MYSQL.

  • I have a table, DIM_BANK.

  • The primary key is the column ID_BANK. MySQL automatically created the an index for this with the following attributes – BTREE (CLUSTERED), UNIQUE.

  • I create a regular index on the varchar column DESC_BANK, with the following attributes – BTREE NOT UNIQUE.

  • When i execute my query, the results are shown in order by DESC_BANK.

My question is why are they shown in order by DESC_BANK? As I understand clustered indexes, they enforce the ordering of the rows of the table physically.

I’m using the InnoDB engine in MySQL 8.0.

Answer :

they enforce the ordering of the rows of the table physically

In fact most RDBMS do not order the results by default until ORDER BY is declared explicitly. Some ordered results can occure occasionally because of coincidence, say if you SELECT the data previously INSERTed in some order. But that behaviour isn’t reliable and you can’t count on it without ORDER BY clause. You should suggest any result without ORDER BY as randomly shuffled to avoid unforseen consequencies.

Leave a Reply

Your email address will not be published. Required fields are marked *