MySQL SUM related to parent query

Use a LEFT JOIN and check for null values. Something like:

SELECT profile.* FROM user_profile profile
 LEFT JOIN user_friend friend1 ON friend1.user_profile1_id = user_profile_id
 LEFT JOIN user_friend friend2 ON friend2.user_profile2_id = user_profile_id
 WHERE friend1.user_profile1_id IS NULL AND friend2.user_profile2_id IS NULL

Here's a link to the various joins

I noticed something interesting about your query: you are getting these totals for this year. The trendCut date is not a static value. It is a very bad candidate for an index column since the column value changes and it is neither first nor last in the column order of the index.

The index you need is this

monitorID,trendCut,nGram,nGramWord,total

However, this would still be deficient.

Suggestion: If you do queries that require trendCut by year, you should have a trendCutYear column. Try the following:

• Create New wordtrend Table
• Create TrendCutYear Column in the New wordtrend Table
• Create New Index in the New wordtrend Table
• Load the New wordtrend Table from the Old wordtrend Table
• Keep the Old wordtrend table under another name

Here are those steps:

CREATE TABLE wordtrendnew LIKE wordtrend;
ALTER TABLE wordtrendnew ADD COLUMN trendCutYear SMALLINT NOT NULL;
ALTER TABLE wordtrendnew ADD INDEX (monitorID,trendCutYear,nGram,nGramWord,total);
ALTER TABLE wordtrendnew DISABLE KEYS;
INSERT INTO wordtrendnew
SELECT oid,monitorId,nGram,nGramWord,negatives,
neutrals,positives,total,trendCut,YEAR(trendCut)
FROM wordtrend;
ALTER TABLE wordtrendnew ENABLE KEYS;
ALTER TABLE wordtrend RENAME wordtrendold;
ALTER TABLE wordtrendnew RENAME wordtrend;

Now, you have a static trendCutYear. You can now query using a static value in the middle of the index. The query can now be rewritten using trendCutYear instead of trendCut:

SELECT nGram, nGramWord, SUM(total) AS sTotal,
SUM(positives), SUM(negatives), SUM(neutrals) FROM WordTrend
WHERE monitorId = 21751021 AND trendCutYear = 2011
GROUP BY nGram, nGramWord ORDER BY sTotal DESC;

This should perform a whole lot better for you.

Give it a Try !!!