MySQL - How to change the order of rows in the table

How to change the order of rows in order to have the rows ordered by custom_field?

For example I have got table with id asc and would like to have the rows in the desc order. I need it in order not to use the 'order by id desc' in the mysql query to optimize the query speed.

I tried:

insert table_with_ordered_rows() 
select * from table_with_not_ordered_row开发者_开发技巧s order by id desc;

but it just copy the table with standard asc order.

The only way to get the sorted result set in mysql - is to add ORDER BY in query.

in the mysql query to optimize the query speed.

It is incorrect. If you have issues with query performance - then ask about query performance. Give us complete table structure, the query, the explain and statistics about data.

The only way to guarantee order in a result set is to use an ORDER BY clause. An ORDER BY can make use of an index, if one exists...

That said, the following worked for me on MySQL 5.1.49:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1$$

INSERT INTO t1 (col) VALUES ('a'),('b'),('c');

Result set:

id  col
--------
1   a
2   b
3   c

Intermediate table:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col` varchar(45) DEFAULT NULL,
  `old_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1$$

INSERT INTO t2 (col, old_id)
  SELECT t.col, t.id
    FROM t1 t
ORDER BY t.id DESC

Result set:

id  col  old_id
----------------
1   c    3
2   b    2
3   a    1

ALTER `tablename` ORDER BY `orderField` DESC;

But you must execute this query after each insert. See 13.1.7. ALTER TABLE Syntax.