问题描述
在 stackoverflow 上的开发人员的帮助下,我已经能够优化我的查询以使其性能比最初更好.执行时间下降到:1.2s
With the help of developers here on stackoverflow, I have been able to optimize my query to perform better than it was initially doing. Execution time dropped to: 1.2s
但是,在对查询执行 EXPLAIN 后,似乎没有使用 loan_applications_tbl ( date_updated, Loan_status, current_loan, ippis )
索引,因为类型列对表 表示 ALL一个
.请问这可能是什么原因?为什么该表的索引被忽略,我该如何解决?
However, after doing EXPLAIN on the query, it seemed like the loan_applications_tbl ( date_updated, loan_status, current_loan, ippis )
indexes were not being used, as the type column says ALL for table a
. What might be the cause for this please? Why is the index for this table being ignored and how do I fix this?
在下面查看我的查询:
EXPLAIN
SELECT
a.id,
a.user_unique_id,
a.loan_location,
a.ippis,
a.tel_no,
a.organisation,
a.branch,
a.loan_agree,
a.loan_type,
a.appr,
a.sold,
a.loan_status,
a.top_up,
a.current_loan,
a.date_created,
a.date_updated,
c.loan_id,
c.user_unique_id tu_user_unique_id,
c.ippis tu_ippis,
c.top_up_approved,
c.loan_type tu_loan_type,
c.dse,
c.status,
c.current_loan tu_current_loan,
c.record_category,
c.date_created tu_date_created,
c.date_updated tu_date_updated
FROM
-- this creates inline mySQL variables I can use for the WHERE condition
-- by doing comma after with no explicit join, it is a single row
-- and thus no Cartesian result, just @variables available now
( select
-- first truncating any TIME portion by casting to DATE()
@myToday := date(curdate()),
-- now subtract day of month -1 to get first of THIS month
@beginOfMonth := date_sub( @myToday, interval dayOfMonth( @myToday ) -1 day ),
-- and now, add 1 month for beginning of next
@beginNextMonth := date_add( @beginOfMonth, interval 1 month ) ) SqlVars,
loan_applications_tbl a
LEFT JOIN topup_or_reapplication_tbl c
ON a.ippis = c.ippis
AND c.current_loan='1'
AND c.status IN ('pending', 'corrected', 'Rejected',
'Processing', 'Captured', 'Reviewed', 'top up')
AND @beginOfMonth <= c.date_updated
AND c.date_updated < @beginNextMonth
WHERE
-- forces only activity for the single month in question
-- since the "a" table knows of any "updates" to the "C",
-- its updated basis will keep overall restriction to any accounts
-- updated within this month in question only
@beginOfMonth <= a.date_updated
AND a.date_updated < @beginNextMonth
-- and NOW we can easily apply the OR without requiring
-- to run against the ENTIRE set of BOTH tables.
AND (
c.ippis IS NOT NULL
OR
( a.loan_status IN ( 'pending', 'corrected', 'Rejected', 'Processing',
'Captured', 'Reviewed', 'top up')
AND (
a.current_loan = '1'
OR ( a.current_loan = '0'
AND a.loan_status IN ('Approved', 'Closed')
)
)
)
)
使用的索引:
loan_applications_tbl ( date_updated, loan_status, current_loan, ippis )
topup_or_reapplication_tbl ( ippis, status, current_loan, date_updated )
对查询的解释给出以下屏幕截图:
EXPLAIN on the query gives the screenshot below:
推荐答案
例如
SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;
这篇关于优化我的查询(索引,EXPLAIN)Mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!