多重数据组合,分页
anchor表
id | user_id | status | video_recom |
---|---|---|---|
1 | 1 | 1 | 0 |
user表
id | login_state | is_zombie | is_virtual |
---|---|---|---|
1 | 1 | 0 | 0 |
SELECT
IF (u.login_state = 1 AND u.is_zombie = 0 AND u.is_virtual = 0,1,
IF (u.login_state = 1 AND u.is_zombie = 0 AND u.is_virtual = 1 AND a.video_recom = 1,2,
IF (u.login_state = 0 AND u.is_zombie = 0 AND u.is_virtual = 0,3,3))) AS flot,
a.user_id,a.single_coin,a.gift_total,a.level
,a.video_state,a.video_recom,v.id AS vid,u.is_virtual,u.is_zombie,
u.user_nickname,u.sex,u.avatar,u.signature,u.birthday,u.more,u.city,u.province,u.login_state
FROM
yz_live_anchor AS a
LEFT JOIN yz_user AS u ON u.id = a.user_id
LEFT JOIN yz_live_video AS v ON v.anchor_id = u.id
AND v.end_time = 0
WHERE status
= 1
ORDER BY flot,a.user_id DESC LIMIT 0,10;
表的数据量也就万数条左右,但是执行时间却在1.6s左右了。怎么可以改进的小一点呢
推荐文章: