MySQL Heap Table

All we need is an easy explanation of the problem, so here it is.

I have learnt that table can be created with order, like adding primary key. On the other hand, heap table is unordered, as mentioned in this page: https://www.sqlshack.com/clustered-index-vs-heap.

However, seems Heap Table is Mysql only refer to table in memory. Reference: https://www.oreilly.com/library/view/mysql-reference-manual/0596002653/ch07s04.html.

So does MYSQL database have any terminology for unordered table?

Please correct me if I get any wrong concepts. Thanks.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

That Oreilly link is so old that it has a syntax error. (TYPE=HEAP became ENGINE=MEMORY about 2 decades ago.)

The default indexing of an ENGINE=MEMORY table is a heap. It does not provide persistence, so it is only useful for certain temporary purposes.

For most uses, you should use ENGINE=InnoDB and have a PRIMARY KEY, which will be BTree-organized.

"Heap" is not necessarily better than "BTree". And it is much worse in a main way: the lack of ordering. Looking up a single row in Hash-organized table is not necessarily faster than in a BTree-organized table. These points lead to why MySQL did not bother to implement Hash as a significant option.

Anyway, Hash is not really "unordered"; instead, let’s say "hard to predict".

A table (or subquery) is technically unordered. That is, the order of the results is not guaranteed unless you explicitly use ORDER BY. The implementation may deliver rows in a predictable order. But that is usually because the Optimizer happened to do something predictable.

To get an unordered list,

  • Simply perform SELECT without an ORDER BY — in some cases, it will ignore the BTree ordering and provide a seemingly random order.
  • Or, Use ORDER BY RAND() to explicitly shuffle the rows. (Note: the same query will give a different ordering each time.)

Why do you "want" an unordered table? Seriously; if you have some goal, there may be a simple workaround.

Another thing to note: MySQL (ENGINE=InnoDB) "clusters" the PRIMARY KEY with the data, thereby forcing an order on the data. Some other engines and vendors do not do this "clustering".

For example, MySQL’s deprecated ENGINE=MyISAM stores the data in the order of insertion. The PRIMARY KEY, if present, is simply a lookup table like any secondary index. (Caveat: Deletes and Updates mess with the ordering.)

Method 2

One of your links is for SQL Server, the other for MySQL.
Only in-memory or temporary tables can be heap tables in MySQL because clustered indexes are assigned automatically in MySQL.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply