Internal Resorting

Anon

I'm sorry if this has been asked before, but is there a way to interntally resort rows withen MySQL? If not, is there a way to do it without loading all the data into a temporary table, and then reinserting it back into the original table? If it at all matters, this table contains an autoincriment field.

thanks in advance.
-mark

Anon

The only way to warrant a specific ordering of data is to use an order by clause in your select statement. It does not matter how you have inserted your values into the table.

The concpet of an internal sort order in a relational database is misconceived.

If you wish to speed up ordering by an non primary key column, add an index (and use an order by clause).

Anon

Ok, I should have phrased the questions more along the lines of 'how do I internally resort a key based on a specific value in that row'

Anon

You can do:

ALTER TABLE MyTable ORDER BY MyField;

But I would question why you're doing this... why not just ORDER BY on your SELECT?

tomhath

When you execute a SELECT statement it returns rows (the result set). If you want the result set ordered you add an ORDER BY statement to the SELECT.

Some dbms' like MS SQL Server allow you to create a "clustered" index which order the rows the same as the index to make retrieval on that index fastest, but I don't think mysql has any concept of "internal sort".

Trying to 'resort a key based on a specific value in that row' makes no sense to me.

Anon

First off - I got around this with some creative uses of the tables. I need to do this because I am making a search tool for a game (www.planetarion.com) and need the rank of each planet/galaxy. Unfortunatly, they only provide people coordinates, names and score, so we have to sort the tables out on our own from those textfiles.
Trust me, I use Order By enough as is 🙂