1

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?

Community
  • 1
  • 1
Gruber
  • 4,478
  • 6
  • 47
  • 74
  • You show us an `INSERT ... SELECT` statement but yet you claim you are using a cursor. Which is true? When using `insert ... select` it does not make any sense to use an `order by`. Why do you need an order by when using a cursor? –  Feb 13 '13 at 12:34
  • @a_horse_with_no_name: I use a cursor on `MyTable` which got its data from a `SELECT` with `ORDER BY`. So both are true. – Gruber Feb 13 '13 at 12:36
  • So in fact you are **not** using `insert .. select`. But why do you (think) you need that order by? –  Feb 13 '13 at 12:40
  • @a_horse_with_no_name: The order is crucial because I implement [maxim1000's algorithm](http://stackoverflow.com/questions/14212593/finding-overlapping-intervals-when-overlaps-are-rare]) in SQL, which has to do with finding overlapping intervals. It is more like procedural code, with regular SQL throwed in here and there to handle sets. It's very handy to avoid using a procedural langugage since the data originates from the database, and the output is to be stored in the same database. – Gruber Feb 13 '13 at 12:44
  • a_horse_with_no_name: I do use `INSERT...SELECT`: `INSERT INTO MyTable SELECT * FROM MyOtherTable ORDER BY Col1, Col2 DESC;` And then the cursor: `DECLARE cur CURSOR FOR SELECT * FROM MyTable`. – Gruber Feb 13 '13 at 12:46
  • Remove the `order by` from the first step. It's totally useless. –  Feb 13 '13 at 12:48
  • @a_horse_with_no_name: Yes, I've understood that. Oddly, it did have effect in MySQL 5.0. – Gruber Feb 13 '13 at 12:49

1 Answers1

2

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

Fabian Schmengler
  • 24,155
  • 9
  • 79
  • 111
  • Good idea. I added an index for the relevant columns, but saw only marginal effect on performance. The difference from using a cursor without `ORDER BY` is striking. – Gruber Feb 13 '13 at 12:38
  • I have to give you more credit. The routine is actually runnable now. – Gruber Feb 13 '13 at 13:23