【MYSQL】Mysql长时间SQL优化一例（force index）

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;