mysql优化发票余额查询

Question

如何优化以下查询。尽管这解决了问题，但有人能帮助我优化查询吗。

select invoice_tbl.invoice_no, invoice_tbl.invoice_date , adddate(invoice_date , interval remdays day) as remainder_date , remainder_tbl.desc
from ( select max(id) as id,invoice_no
from invoice_tbl inv
inner join remainder_tbl where adddate(invoice_date , interval remdays day) <= now() group by invoice_no ) as remdetails
inner join invoice_tbl on invoice_tbl.invoice_no = remdetails.invoice_no
inner join remainder_tbl on remainder_tbl.id = remdetails.id

我们有两个表，invoice_tbl和reminder_tbl。

 invoice_tbl
 -----------
*--------------------------------------------*
| invoice_no  |  invoice_date | patient_name |
|-------------|---------------|--------------|
| 1           | 2015-07-15    | xxxxxxxxxxxx |
| 2           | 2015-07-29    | aaaaaaaaaaaa |
*-------------*---------------*--------------*
remainder_tbl
-------------
*----------------------------------------*
| id  |  remdays | desc                  |
|-----|----------|-----------------------|
| 1   |  5       | First Remainder       |
| 2   |  8       | Second Remainder      |
| 3   |  10      | Third Remainder       |
| 4   |  15      | Fourth Remainder      |
*-----*----------*-----------------------*

当我运行查询时，它应该显示为

*------------------------------------------------------*
| invoice_no | invoice_date | remainder_date | desc    |   
|------------|--------------|----------------|---------|
| 1          | 2015-07-15   | 2015-07-30     | Fourth  |
| 2          | 2015-07-29   | 2015-08-06     | Second  |
*------------*--------------*----------------*---------*

Answer 1

adddate(invoice_date , interval remdays day) <= now()

在函数调用中隐藏了invoice_date，因此无法使用INDEXing。更改为

NOW() - INTERVAL remdays DAY <= invoice_date

并为每个表提供SHOW CREATE TABLE，以便我们可以检查正确的索引。

并提供EXPLAIN SELECT ...，这样我们就可以看到优化器决定做什么。