如何修复慢速sql查询

I'm making a wp guestbook and i've made a mistake somewhere. At first i've tried to use join but coud not get it to work properly because of all those conditions so i wrote this:

我正在制作一本wp留言簿,我在某个地方犯了一个错误。起初我已经尝试使用join但是由于所有这些条件而无法让它正常工作所以我写了这个:

$query = "  
        (select *,
        (select count(cid) from ctable WHERE nid = vid) as posts,
        (select timestamp from ctable where nid = vid order by timestamp desc limit 1) as lt,
        (select count(vid) from ntable) as total
        FROM ntable
        )";

It does exactly what it supossed to do but it is very slow. I know i shout use join but i cant figure it out.

它确实完成了它所做的事情,但它非常缓慢。我知道我大喊使用加入,但我无法弄明白。

3 个解决方案

#1

As per my Exp level ..try to use the nolocks as I updated your query that is ,

按照我的Exp级别..来使用nolocks,因为我更新了你的查询,

$query = '(select *,
        (select count(cid) from ctable with(nolock) WHERE nid = vid) as posts,
        (select timestamp from ctable with(nolock) where nid = vid order by timestamp desc limit 1) as lt,
        (select count(vid) from ntable with(nolock)) as total
        FROM ntable with(nolock)
        )';

and you are using the Order by for with timestamp column and instead of that use getdate()-1(means days count for the old data so that query will improve its performance.)

并且您使用Order by for timestamp列而不是使用getdate() - 1(表示旧数据的天数,以便查询将提高其性能。)

#2

A join query might look like this (hard to be sure because you have not provided table definitions)

连接查询可能看起来像这样(很难确定,因为您没有提供表定义)

select *,p.posts,t.ts,(select count(*) from ntable) total 
 from ntable
 join (select vid,count(*) posts from ctable group by vid) p on p.nid = vid
 join (select vid,max(timestamp) ts from ctable group by vid) t on t.nid = vid

but assuming you have indexes on nid and vid I doubt if you will see an improvment - I would be interested to know.

但假设你有关于nid和vid的索引我怀疑你是否会看到一个改进 - 我很想知道。

#3

It doesn't. The problem is not in getting the data that is fast enough the problem is with order in with they are displayed (lt). that takes the longest.

它没有。问题不在于获得足够快的数据,问题在于显示它们的顺序(lt)。花费的时间最长。

#1