select max(data.id) id, data.node_mac, node.name node_name, max(data.up_date) up_date,
group_concat(data.temperature order by up_date desc separator ',') as temperature,
group_concat(data.humidity order by up_date desc separator ',') as humidity,
group_concat(data.pressure order by up_date desc separator ',') as pressure,
group_concat(data.illumination order by up_date desc separator ',') as illumination,
group_concat(data.CO2 order by up_date desc separator ',') as CO2,
group_concat(data.winddir order by up_date desc separator ',') as winddir,
group_concat(data.windspeed order by up_date desc separator ',') as windspeed,
group_concat(data.PM25 order by up_date desc separator ',') as PM25,
group_concat(data.PM10 order by up_date desc separator',' ) as PM10,
group_concat(data.nxygenion order by up_date desc separator ',') as nxygenion
from t_data data
left join t_node node on data.node_mac = node.mac
inner join t_modulename modulename on modulename.module_name = node.name
group by node_mac
5 个解决方案
#1
其中,node_mac、up_date已经建立索引
#2
即使只查询一个内容,也需要2秒:
select max(data.id) id, data.node_mac, node.name node_name, max(data.up_date) up_date,
group_concat(data.temperature order by up_date desc separator ',') as temperature
from t_data data
left join t_node node on data.node_mac = node.mac
inner join t_modulename modulename on modulename.module_name = node.name
group by node_mac
select max(data.id) id, data.node_mac, node.name node_name, max(data.up_date) up_date,
group_concat(data.temperature order by up_date desc separator ',') as temperature
from t_data data
left join t_node node on data.node_mac = node.mac
inner join t_modulename modulename on modulename.module_name = node.name
group by node_mac
#3
贴出 explain select ... 显示一下执行计划以分析效率。
#4
这么冗长的句子。。表设计的不太合理,尝试下修改表吧
#5
Creating Sort Index 3.9 s 65.60% 1 3.9 s
sending data 1.9 s 34.00%
#1
其中,node_mac、up_date已经建立索引
#2
即使只查询一个内容,也需要2秒:
select max(data.id) id, data.node_mac, node.name node_name, max(data.up_date) up_date,
group_concat(data.temperature order by up_date desc separator ',') as temperature
from t_data data
left join t_node node on data.node_mac = node.mac
inner join t_modulename modulename on modulename.module_name = node.name
group by node_mac
select max(data.id) id, data.node_mac, node.name node_name, max(data.up_date) up_date,
group_concat(data.temperature order by up_date desc separator ',') as temperature
from t_data data
left join t_node node on data.node_mac = node.mac
inner join t_modulename modulename on modulename.module_name = node.name
group by node_mac
#3
贴出 explain select ... 显示一下执行计划以分析效率。
#4
这么冗长的句子。。表设计的不太合理,尝试下修改表吧
#5
Creating Sort Index 3.9 s 65.60% 1 3.9 s
sending data 1.9 s 34.00%