mysql - Mysql查询加入5个股票表

Question

我有 5 张桌子；债务权益、股息收益、市场上限、市场数据和 p_e_ratio。我想加入他们，每个表都有一个公司符号列。我想做一个查询并加入这 5 个很快的表。我试过了

SELECT *, _pe.date as pe_date, _md.Close, _mc.price as market_cap_price, yield FROM `debt_equity` m
INNER join p_e_ratio _pe on _pe.company = m.company
INNER join market_data _md on _md.company = m.company
INNER join market_cap _mc on _mc.company = m.company
INNER join dividend_yield _y on _y.company = m.company 
where m.date >'2020-03-31' and m.date < '2020-06-31' 
and m.price < 1 and _pe.date >'2020-03-31' and _pe.date < '2020-06-31' and _pe.ratio < 30
and _md.Close >= 15 and _md.date_time >'2020-03-31' and _md.date_time < '2020-06-31'
and _mc.company = m.company and _mc.price > 2000 and _mc.date >'2020-03-31' and _mc.date < '2020-06-31'and  _mc.date <= m.date
and _y.yield > 8  and _y.date >'2020-03-31' and _y.date < '2020-06-31'
GROUP by m.company order by m.company asc

但是太慢了

我更新的sql查询性能更好添加了更多的索引

SELECT *, _pe.date as pe_date, _mc.price as market_cap_price, yield FROM `debt_equity` m
INNER join p_e_ratio _pe on _pe.company = m.company and (_pe.date >= DATE_sub(m.date, INTERVAL 10 day ) and _pe.date <= m.date)  and _pe.ratio < 30
INNER join market_cap _mc on _mc.company = m.company and (_mc.date >= DATE_sub(m.date, INTERVAL 30 day ) and _mc.date <= m.date) and _mc.price > 2000
INNER join dividend_yield _y on _y.company = m.company and (_y.date >= DATE_sub(m.date, INTERVAL 10 day ) and _y.date <= m.date) and _y.yield > 8

INNER join market_data _md on _md.company = m.company and (_md.date_time >= DATE_sub(m.date, INTERVAL 2 day ) and _md.date_time <= DATE_add(m.date, INTERVAL 2 day )) and  _md.Close >= 15
where m.date >'2020-03-31' and m.date < '2020-07-31' and m.price < 1
GROUP by m.company order by m.company ASC

标签： mysqlsql