In Mx 7.14.1 the index configuration is extended to specify for each attribute the sorting direction of the index: release notes. To change the index in the Modeler you need to upgrade or otherwise change in the database configuration.
In those kinds of cases consider adding additional indexes by hand.
There are so many different types of indexes, and different ways of configuring those indexes that simply adding all those combinations makes indexes to complex to configure in the platform. The index configuration we have in the platform works for most cases.
If for that specific scenario you need different indexes just add them directly to the database (in 7 you have jdbc access to do/check this at startup). For another project with 30+ million records in an entity we added a custom index on the table and got the (contains) search on that datagrid down to 200ms (from 20+ seconds without indexes).
Due to the custom nature and expertise necessary to add the right indexes, at least for now, we chose to not include this in the standard index functionality. (Adding those things wrong can ruin your database response).
I can answer your specific question " Why is Mendix adding the ID column to every sort? Idea's to work around this issue? ".
Datagrids are paginated. If you leave our the sort on an attribute that's guaranteed to be unique (which id is), you will get undesired results with pagination. If you leave our the sort by id, the database is allowed to use any ordering for every individual query as long as it still conforms to your specified order by.
Let's say you have 100 records in your database. Fifty of those have a=Henk and 50 of those have a=Herbert.
If you show 10 records per page and you would only order by a, the database might show the same 10 records for the first 5 pages and the same 10 records for the last 5 pages. This seems counterintuitive but makes sense if you think about what is the fastest way for the database to come up with results that meet the specified order by criteria.
This behaviour will be different in practice for different dba vendors, but according to the ansi sql specs (or whatever they're called) you get no guarantees of having any sensible sorting if you don't order by at least one unique attribute.