带连接的mysql查询，order by和limits

I am running a query in mysql which contains JOIN, ORDER BY and LIMIT, The Query is

我在mysql中运行一个查询,其中包含JOIN,ORDER BY和LIMIT,查询是

SELECT r.* FROM register r INNER JOIN payments p ON r.matri_id = p.pmatri_id WHERE p.p_plan LIKE '%Free%' ORDER BY r.index_id DESC LIMIT 0, 10

SELECT r。* FROM register r INNER JOIN payment p ON r.matri_id = p.pmatri_id WHERE p.p_plan LIKE'%Free%'ORDER BY r.index_id DESC LIMIT 0,10

where register table contains almost 5098 Records and payments table contains almost 5023 records, Now the thing happening is when I am running this Query it's taking ages to take the result Its taking almost 102 seconds to 147 seconds (If I am putting the limit on 10 too). And When I am trying to run

其中寄存器表包含近5098个记录和付款表包含近5023条记录,现在发生的事情就是当我运行此查询时需要花费很长时间才能获得结果它花了将近102秒到147秒(如果我将限制放在10上太)。而当我试图跑

select * from payments or select * from register

从付款中选择*或从寄存器中选择*

query from register or payments, the whole table results comes within 0.01 seconds.

从寄存器或付款查询,整个表结果在0.01秒内。

I had also tried modifying the above query, everything is working fine if I am removing the ORDER BY, After Removing the order by with joins, its taking only 0.04 seconds or less..

我也尝试修改上面的查询,如果我删除ORDER BY,一切正常,在通过连接删除订单后,它只需要0.04秒或更短时间..

I don't know where I am missing or where I have left something, Please review the query and tell me the solution.

我不知道我遗失的地方或遗留的地方,请查看查询并告诉我解决方案。

Thanks in Advance...

提前致谢...

1 个解决方案

#1

Just a re-write, using left joins.

只需重写一次,使用左连接。

SELECT r.*, 
m.mtongue_id, m.mtongue_name, 
rel.religion_id, rel.religion_name, 
c.caste_id, c.caste_name, 
con.country_id, con.country_name,
st.state_id, st.state_name, 
ct.city_id, ct.city_name, 
des.desg_id, des.desg_name, 
ocp.ocp_id, ocp.ocp_name 
FROM register r
LEFT JOIN mothertongue m ON r.m_tongue = m.mtongue_id
LEFT JOIN religion rel ON r.religion = rel.religion_id
LEFT JOIN caste c ON r.caste = c.caste_id
LEFT JOIN country con ON r.country_id = con.country_id
LEFT JOIN state st ON r.state_id = st.state_id 
LEFT JOIN city ct ON r.city = ct.city_id
LEFT JOIN designation des ON r.designation = des.desg_id
LEFT JOIN occupation ocp ON r.occupation = ocp.ocp_id
WHERE r.matri_id = 'MF1202'

Assuming that the joined id fields are all the primary key in their table.

假设连接的id字段是其表中的所有主键。

#1