数据库加索引
今天做了一个关联查询最大值的需求,sql代码如下
SELECT *, (SELECT MAX(b.cost) FROM t_jl_material_report b WHERE b.material_id = u.material_id) max_cost FROM t_jl_video_material u WHERE 1 = 1 ORDER BY max_cost DESC LIMIT 0,10
执行sql没问题,缺点就是执行速度慢,优化方式选择对t_jl_material_report加索引,代码如下:
ALTER TABLE
zy_game_system
.t_jl_material_report
ADD INDEXt_jl_material_report_material_id
(material_id
) USING BTREE COMMENT ‘素材报表关联’