Paging optimization example

The NDB engine requires orderby every time to maintain the order.

User_id is the index Column

Original code

Select

SC _user_id userid,

SC _user_nm username,

SC _user_sex usersex,

SC _user_real_name userrealname,

SC _mobile_no mobileno,

Email,

Scl_register_dts registerdts,

Scl_account_balance accountbalance,

Scl_recharge_amt rechargeamt,

Scl_invest_amt investamt,

Scx_redemption_amt redemptionamt,

Scx_freeze_amt freezeamt,

Scx_freeze_amtf withdrawamt,

Create_dtss createdts,

Update_dtstime updatedts

From tbl_custdserv_7user

Where update_dtstime> = Concat ('20160301', '20160301 ')

Order by SC _user_nm

Limit 35950,50;

Execution time: 7.45 seconds

========================================================== ======================================

Optimized Code

Select

A. SC _user_id userid,

A. SC _user_nm username,

A. SC _user_sex usersex,

A. SC _user_real_name userrealname,

A. SC _mobile_no mobileno,

A. email,

A. scl_register_dts registerdts,

A. scl_account_balance accountbalance,

A. scl_recharge_amt rechargeamt,

A. scl_invest_amt investamt,

A. scx_redemption_amt redemptionamt,

A. scx_freeze_amt freezeamt,

A. scx_freeze_amtf withdrawamt,

A. create_dtss createdts,

A. update_dtstime updatedts

From tbl_custdserv_7user

Join

 

(Select

SC _user_id,

SC _user_nm

From tbl_custdserv_7user

Where update_dtstime> = Concat ('20160301', '20160301 ')

Order by SC _user_nm

Limit 35950,50) B

On a. SC _user_id = B. user_id

Execution time: 0.2 seconds

Failed to try on a. SC _user_nm = B. SC _user_nm

This article from the "deep mountains with dba" blog, please be sure to keep this source http://19801021.blog.51cto.com/7262596/1567147

Paging optimization example