ALTER TABLE `table` ORDER BY `column_a` DESC

[boonkit]

I try to get better performance by implementing this:

http://dev.mysql.com/doc/mysql/en/alter-table.html

"ORDER BY allows you to create the new table with the rows in a
specific order. Note that the table does not remain in this order after
inserts and deletes. This option is mainly useful when you know that
you are mostly going to query the rows in a certain order; by using
this option after big changes to the table, you might be able to get
higher performance. In some cases, it might make sorting easier for
MySQL if the table is in order by the column that you want to order it
by later."

Here is the steps:

I run this SQL query on one of the table:
"ALTER TABLE `table` ORDER BY `column_a` DESC"

Then, i browse the table:
"SELECT * FROM `table` LIMIT 0, 30"
The rows of data is arranged in expected order (column_a DESC)

However when i add a where definition:
"SELECT * FROM `table` WHERE column_b = 'b' LIMIT 0, 30"
Then, the result is arranged in PRIMARY KEY ASC

So, if i want the result to display in order of 'column_a DESC', i have
to append 'ORDER BY column_a DESC' at SQL query, and this will affect
the performance.

Am i did anything wrong?
Or
Someone please advise me on how to make use of the advantage as
described at 'http://dev.mysql.com/doc/mysql/en/alter-table.html

FYI. i running MySQL 4.0.24 on Linux Fedora 3

[Bill Karwin]

bo*****@gmail.c om wrote:

Someone please advise me on how to make use of the advantage as
described at 'http://dev.mysql.com/doc/mysql/en/alter-table.html

I would never use that feature. It isn't reliable, as they describe in
the web page, because it changes if you change the data in the table.
Queries should behave consistently given the same set of data. For the
query to behave differently based on the history of changes to the table
breaks consistency. This is not good for a relational database (yeah, I
know MySQL doesn't pass the ACID test by default, but this makes it
worse than usual).

I can't imagine that the difference in performance would be sufficiently
greater than what you can get by using SELECT ... ORDER BY over an
indexed field to justify the hassle in keeping it working.

IMHO, that feature is useless as it is implemented, and it should be
avoided.

Put ORDER BY clauses in all of your SELECT queries where the order is
important. That method is reliable.

Regards,
Bill K.