SELECT SUBSTR(eff_time,1,10),ns_name,COUNT(*) FROM HEHEDA WHERE isActive = 1 AND stype <> 'report' AND usefull = 1
AND eff_time > '2014-01-01' AND itype =1
AND ns_name IN ('凤凰网', '新浪财经', '和讯网', '金融界', '腾讯网', '中国经济网', '财经网', '中证网', '中国证券网', '中金在线', '经济观察网')
GROUP BY SUBSTR(eff_time,1,10),ns_name ORDER BY ns_name,SUBSTR(eff_time,1,10) DESC;
show index from HEHEDA;
Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
1 ID A 31246724 BTREE
1 news_url A 31246724 150 YES BTREE
2 stype A 31246724 YES BTREE
1 group_id A 31246724 BTREE
1 news_id A 31246724 BTREE
2 has_summary A 31246724 YES BTREE
3 stype A 31246724 YES BTREE
1 insert_time A 31246724 YES BTREE
1 news_fetch_time A 31246724 YES BTREE
1 update_time A 31246724 YES BTREE
1 news_publish_time A 31246724 YES BTREE
2 news_id A 31246724 BTREE
1 ns_name A 54247 YES BTREE
2 insert_time A 31246724 YES BTREE
1 ns_name A 78707 YES BTREE
2 news_title A 31246724 255 YES BTREE
3 news_publish_time A 31246724 YES BTREE
1 itype A 3188 YES BTREE
1 eff_time A 15623362 YES BTREE
1 ns_name A 86079 YES BTREE
2 itype A 72666 YES BTREE
1 itype A 1200 YES BTREE
2 stype A 2402 YES BTREE
3 ns_name A 164456 YES BTREE
--修改SQL
SELECT
SUBSTR(eff_time, 1, 10),
ns_name,
COUNT(*)
FROM
HEHEDA FORCE INDEX (HEHEDA_ix_10)
WHERE itype = 1
AND stype <> 'report'
AND ns_name IN (
'凤凰网',
'新浪财经',
'和讯网',
'金融界',
'腾讯网',
'中国经济网',
'财经网',
'中证网',
'中国证券网',
'中金在线',
'经济观察网'
)
AND isActive = 1
AND usefull = 1
AND eff_time > '2014-01-01'
GROUP BY
SUBSTR(eff_time, 1, 10),
ns_name
ORDER BY
ns_name,
SUBSTR(eff_time, 1, 10) DESC;
【MYSQL】Mysql长时间SQL优化一例(force index)
最新推荐文章于 2024-04-15 16:13:05 发布