mysql - 查询速度非常慢

Question

我对 mysql 完全陌生，一直在尝试运行下面的 mysql 查询，但它似乎运行得非常慢，需要几个小时。该表大约 100MB，包含大约 200 万行。

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)

)
;

表的索引报告；

"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" ""  ""

我做错了什么或者我错过了什么才让它变得这么慢？

Answer 1

我可以想象的一件事是 MySQL 不使用索引(或使用效率低下)，因为其中一个字段有算术运算。这是猜测。

您可以使用变量编写查询。这不是我最喜欢的方法，但在这种情况下可能有效:

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;