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.
That Oreilly link is so old that it has a syntax error. (
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
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.)
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 🙂