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?

could be that long CASE. Maybe try creating the table, then inserting the data separately? I don't know. — Buttle Butkus, Aug 14 '13 at 00:33
If the index is non-clustered, then you might want to either make it clustered, or add "Price" as an included column. Otherwise, the query will need to do repeated, costly index scans. — McGarnagle, Aug 14 '13 at 00:49
I don't see anything here that could be an issue. The join isn't bad since you have an index on it. Even if it's non-clustered, it's only 2 million lookups. The case is fairly simple. So I would suspect other issues: Is this table in use? Maybe your process is starved by others locking the table. How is the IO? Maybe it's taking a while to allocate space to create this table or (less likely) to read the source. — John Tseng, Aug 14 '13 at 01:54
Seems to be no I/O activity to the disk to speak of. I presume it must all be resident in memory the myqsl process is using about 500MB(?). — user2676706, Aug 14 '13 at 10:01
It seems to be that left join that is taking all the time, anyone know why? — user2676706, Aug 14 '13 at 13:41

score 2 · Accepted Answer · answered Aug 14 '13 at 03:30

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;

thanks that is amazingly much faster - 20sec rather than 10 hours for the other one at which point I decided to stop. There is however one problem that appears, the price is a decimal (7,3) the price difference is coming back with rounding errors at the end of what looks like a double. E.g. 1.200 - 1.100 = 0.09999999999999432. Is there some funny arithmetic or typing going on? — user2676706, Aug 14 '13 at 10:17
@user2676706 . . . Just use `declare @prevprice` to give it the same type as `price`. The default would be a float, I think. — Gordon Linoff, Aug 14 '13 at 10:49
Yes, that would make sense. How would I declare the variable in this context? presumably I would have to have `Begin declare @prevprice as decimal(7,3); end` at the beginning? — user2676706, Aug 14 '13 at 11:27
@user2676706 . . . You can do that. You might start, though, with using a `cast` on the `NULL` value to the appropriate type. That would be the easiest solution. — Gordon Linoff, Aug 14 '13 at 11:30

Very slow query

1 Answers1