我如何结合这些SQL查询的结果？

I want to combine two queries' results. I used select union but it imposed my web-site. (for the records my php memory limit is 128M) How can i combine these queries' results without imposing server?

我想结合两个查询的结果。我使用了select union,但它强加了我的网站。 (对于记录我的php内存限制为128M)如何在不强加服务器的情况下结合这些查询的结果?

First query

SELECT mybb_posts.pid, mybb_posts.dateline, mybb_posts.subject, mybb_users.username, mybb_users.avatar, mybb_posts.message
FROM mybb_posts, mybb_users
WHERE mybb_posts.uid = mybb_users.uid AND mybb_posts.fid <> 170 AND mybb_posts.fid <> 77 AND mybb_posts.fid <> 96
ORDER BY mybb_posts.dateline DESC

Second query

SELECT ek_yorumlar.id, ek_yorumlar.zaman, ek_yorumlar.icisim, mybb_users.username, mybb_users.avatar, ek_yorumlar.turu
FROM ek_yorumlar, mybb_users
WHERE ek_yorumlar.gonderen = mybb_users.uid
ORDER BY ek_yorumlar.zaman DESC 

Union query

SELECT * FROM(  
SELECT mybb_posts.pid, mybb_posts.dateline, mybb_posts.subject, mybb_users.username, mybb_users.avatar, mybb_posts.message
FROM mybb_posts, mybb_users
WHERE mybb_posts.uid = mybb_users.uid AND mybb_posts.fid <> 170 AND mybb_posts.fid <> 77 AND mybb_posts.fid <> 96
ORDER BY mybb_posts.dateline DESC
) AS T  
UNION ALL
SELECT * FROM (  
SELECT ek_yorumlar.id, ek_yorumlar.zaman, ek_yorumlar.icisim, mybb_users.username, mybb_users.avatar, ek_yorumlar.turu
FROM ek_yorumlar, mybb_users
WHERE ek_yorumlar.gonderen = mybb_users.uid
ORDER BY ek_yorumlar.zaman DESC 
) AS T 
ORDER BY dateline DESC LIMIT 25

1 个解决方案

#1

You are reaching memory limits because this query adds ALL rows that meet your criteria into memory and then reduces it down to 25 rows. If you reduce the rows in each component first, it should reduce the memory burden by only looking at each query half first and then returning 25 rows each so it will only take into account 50 rows.

您正在达到内存限制,因为此查询将符合条件的所有行添加到内存中,然后将其减少到25行。如果首先减少每个组件中的行,它应该通过仅先查看每个查询的一半然后再返回25行来减少内存负担,这样它才会考虑50行。

SELECT * FROM(  
SELECT mybb_posts.pid, mybb_posts.dateline, mybb_posts.subject, mybb_users.username, mybb_users.avatar, mybb_posts.message
FROM mybb_posts
inner join  mybb_users on mybb_posts.uid = mybb_users.uid 
WHERE mybb_posts.fid not in (170 ,77 ,96)
ORDER BY mybb_posts.dateline DESC LIMIT 25
) AS T  
UNION 
SELECT * FROM (  
SELECT ek_yorumlar.id, ek_yorumlar.zaman, ek_yorumlar.icisim, mybb_users.username, mybb_users.avatar, ek_yorumlar.turu
FROM ek_yorumlar
inner join  mybb_users on ek_yorumlar.gonderen = mybb_users.uid
ORDER BY ek_yorumlar.zaman DESC LIMIT 25
) AS T 
ORDER BY dateline DESC LIMIT 25

Alternatively you can create temporary tables as a way of reducing the amount of computation performed in any single step...

或者,您可以创建临时表,以减少在任何单个步骤中执行的计算量...

create temporary table firsthalf
 SELECT mybb_posts.pid, mybb_posts.dateline, mybb_posts.subject, mybb_users.username, mybb_users.avatar, mybb_posts.message
    FROM mybb_posts
    inner join  mybb_users on mybb_posts.uid = mybb_users.uid 
    WHERE mybb_posts.fid not in (170 ,77 ,96)
    ORDER BY mybb_posts.dateline DESC LIMIT 25;
create temporary table secondhalf
    SELECT ek_yorumlar.id, ek_yorumlar.zaman, ek_yorumlar.icisim, mybb_users.username, mybb_users.avatar, ek_yorumlar.turu
    FROM ek_yorumlar
    inner join  mybb_users on ek_yorumlar.gonderen = mybb_users.uid
    ORDER BY ek_yorumlar.zaman DESC LIMIT 25;

select * from firsthalf
union all 
select * from secondhalf
ORDER BY dateline DESC LIMIT 25