Very slow query

Question

I am completely new to mysql and have been trying to run the below mysql query, but it seems to be running incredibly slowly, taking hours. The table is about 100MB and contains about 2 million rows.

Create TEMPORARY table temp_table as 
(
SELECT
  pcur.RECORD_ID,
  pcur.Price,
  (pcur.Price - plast.Price) as 'Price_Difference',
        CASE 
       when plast.Price between 0 and 0.25 then ((pcur.Price - plast.Price)/0.001)
        when plast.Price between 0.2501 and 0.5 then ((pcur.Price - plast.Price)/0.005)
        when plast.Price between 0.5001 and 10 then ((pcur.Price - plast.Price)/0.01)
        when plast.Price between 10.0001 and 20 then ((pcur.Price - plast.Price)/0.02)
        when plast.Price between 20.0001 and 100 then ((pcur.Price - plast.Price)/0.05)
        when plast.Price between 100.0001 and 200 then ((pcur.Price - plast.Price)/0.1)
        when plast.Price between 200.0001 and 500 then ((pcur.Price - plast.Price)/0.2)
        when plast.Price between 500.0001 and 1000 then ((pcur.Price - plast.Price)/0.5)
        when plast.Price between 1000.0001 and 2000 then ((pcur.Price - plast.Price)/1)
        when plast.Price between 2000.0001 and 5000 then ((pcur.Price - plast.Price)/2)
        when plast.Price between 5000.0001 and 9995 then ((pcur.Price - plast.Price)/5)
    END AS Price_Diff_Ticks
FROM
  /* First aliased table is the current record */
  00005 pcur
  /* Second aliased table is the previous one, whose id is one behind */
  LEFT JOIN 00005 plast ON (pcur.Record_ID = plast.Record_ID + 1)

)
;

The table's index reports;

"Table" "Non_unique"    "Key_name"  "Seq_in_index"  "Column_name"   "Collation" "Cardinality"   "Sub_part"  "Packed"    "Null"  "Index_type"    "Comment"   "Index_comment"
============================================================================================
"00005" "0" "PRIMARY"   "1" "RECORD_ID" "A" "2275579"   ""  ""  ""  "BTREE" ""  ""

What am I doing wrong or what have I missed to manage to make it so slow?

Answer 1

One thing that I could imagine is that MySQL does not use the index (or uses it ineffectively) because one of the fields has arithmetic on it. That is speculation.

You can write the query using variables. Not my favorite approach, but it might work in this case:

Create TEMPORARY table temp_table as 
SELECT pcur.RECORD_ID, pcur.Price, (pcur.Price - @prevPrice) as 'Price_Difference',
        CASE 
        when @prevPrice between 0 and 0.25 then ((pcur.Price - @prevPrice)/0.001)
        when @prevPrice between 0.2501 and 0.5 then ((pcur.Price - @prevPrice)/0.005)
        when @prevPrice between 0.5001 and 10 then ((pcur.Price - @prevPrice)/0.01)
        when @prevPrice between 10.0001 and 20 then ((pcur.Price - @prevPrice)/0.02)
        when @prevPrice between 20.0001 and 100 then ((pcur.Price - @prevPrice)/0.05)
        when @prevPrice between 100.0001 and 200 then ((pcur.Price - @prevPrice)/0.1)
        when @prevPrice between 200.0001 and 500 then ((pcur.Price - @prevPrice)/0.2)
        when @prevPrice between 500.0001 and 1000 then ((pcur.Price - @prevPrice)/0.5)
        when @prevPrice between 1000.0001 and 2000 then ((pcur.Price - @prevPrice)/1)
        when @prevPrice between 2000.0001 and 5000 then ((pcur.Price - @prevPrice)/2)
        when @prevPrice between 5000.0001 and 9995 then ((pcur.Price - @prevPrice)/5)
    END AS Price_Diff_Ticks,
    @prevPrice = pcur.Price
FROM /* First aliased table is the current record */
     00005 pcur cross join
     (select @prevprice := NULL) const
order by Record_Id;