Description:
If one wants to sort table rows in specific order
as "query speed" and "index file size" optimizations,
s/he has to call
ALTER TABLE ... ORDER BY ... instead of OPTIMIZE TABLE.
This has certain drawbacks :
1. You have to give extended privileges for ALTER TABLE.
Compare this to simple SELECT and INSERT privileges required by the OPTIMIZE TABLE.
2. OPTIMIZE TABLE is smart - it can decide whether to do an action or skip it.
ALTER TABLE is a dumb procedure in this regard. Even table had not changed
since the last "same" "ALTER TABLE .. ORDER BY .." call,
it will perform the requested operation.
How to repeat:
N/A
Suggested fix:
Adding OPTIMIZE_TABLE_ORDER_BY table option to CREATE TABLE and ALTER TABLE
would handle the gap among statements gracefully.
Then, OPTIMIZE TABLE can look up this option value and behave accordingly.
Note that we can not name the new option simply as "ORDER BY" as
1. Users may think that MySQL will store/give back results in this order.
2. It is already used in ALTER TABLE in the perfect meaning.
OPTIMIZE_TABLE[_HINT]_ORDER_BY, as a name, merely suggest that
this ordering might be performed upon OPTIMIZE TABLE call.
And, it is not in effect on INSERT/UPDATE/DELETE calls.
