CODEWITHSUNDEEP
mysqlsql-order-bysql-insert
Gruber
Gruber

Reputation: 4558

ORDER BY ignored when INSERTing into MySQL table

I just upgraded a MySQL 5.0 server to MySQL 5.5 and found that stored routines that worked before had broken. Difference: MySQL 5.5 seems to INSERT rows in an arbitrary order. So in the following code the ORDER BY clause has no effect. AFAIK, it used to have that in MySQL 5.0.

INSERT INTO MyTable
SELECT * FROM MyOtherTable ORDER BY Col1, Col2 DESC;

People say that, by definition, order is irrelevant in INSERTs: Just use ORDER BY when using SELECT from the table. Problem is I use a cursor to loop the table and perform complex operations. Surely I can put the ORDER BY statement on the cursor definition instead:

DECLARE cur CURSOR FOR SELECT * FROM MyTable ORDER BY Col1, Col2 DESC;

But that slows down the routine: from 10 seconds on MySQL 5.0 to over 10 minutes on MySQL 5.5.

Any ideas on how to solve problem?

Upvotes: 1

Views: 1578

Answers (1)

Fabian Schmengler
Fabian Schmengler

Reputation: 24551

Add an index on (Col1, Col2) to speed up ordering.

Upvotes: 2

Related Questions