I have a slow query problem about 0.3 to 0.7 seconds in some cases. This is like Facebook news feed that contains user posts and page posts from people and pages you subscribed to or are following, user posts have no pageid, page posts have userid and pageid. The slow query seems to affect the pageposts(siteid) not the userposts(userid). It seems like the two instances in “left join sub” table are too much. All columns have indexes.
The no. 2 is the users ID.
The MySQL query looks something like this:
SELECT *, news.userid AS userid, news.time AS time, news.id AS id, news.siteid AS siteid, IFNULL(friends.id, 0) AS isfriend, IFNULL(admins.id, 0) AS isadmin FROM news
LEFT JOIN subscribe AS sub ON sub.suidwho='2'
and
((sub.suidto=news.userid and news.siteid=0)
or (sub.siteid=news.siteid and not news.userid='0' and not news.siteid='0'))
LEFT JOIN friends ON (friends.frs_req_id='2' or friends.frs_req_id=news.userid) and (friends.frs_id='2' or friends.frs_id=news.userid) AND friends.frs_approve='1' and news.siteid='0'
LEFT JOIN admins ON admins.siteid=news.siteid and admins.userid='2' and not news.siteid='0'
WHERE sub.id IS NOT NULL
AND
((news.privacy_type<='2') or (news.privacy_type<='3' and (friends.id IS NOT NULL or admins.id IS NOT NULL)))
group by news.id order by news.id DESC limit 10
EXPLAIN:
1 select_type: SIMPLE news type: index possible_keys: userid,siteid,privacy_type key: PRIMARY key_len: 4 ref: NULL rows: 1 Extra: Using where
1 select_type: SIMPLE sub type: ref possible_keys: PRIMARY,suidto,suidwho,siteid key:suidwho key_len: 4 ref: const rows: 1 Extra: Using index condition; Using where
1 select_type: SIMPLE friends type: ref possible_keys: frs_id,frs_req_id,frs_approve key:frs_approve key_len: 4 ref: const rows: 35 Extra: Using where
1 select_type: SIMPLE admins type: ref possible_keys: userid,siteid key: userid key_len: 4 ref: const rows: 1 Extra: Using where
Can anyone please help me to solve this problem or ask more info to solve this, or am I doing something wrong?
The problem here is that the order by news.id DESC (the desc part), ordering the index makes it slow. Or I can just remove the page part and show only user posts or reverse and so it will use userid index or siteid index, but using both at the same time makes it slow and so the PRIMARY key index is used only, not the siteid or userid.
You think that this is making it slow, just ignore it, I just edit it all out?
I have tried somehow order the results without order by, that turned out impossible.
I have to split this to two queries(one for the siteid(uses this index) and one for the userid(uses this index), see the LEFT join SUB) or use “MySQL multi query”(never used it before, don’t know if it’s possible)(this sxxx ).
I don’t know, searching for answers and trying, editing current code, it’s too slow!
select news.userid as userid FROM news
LEFT JOIN subscribe as sub ON sub.suidwho='999'
and
((sub.suidto=news.userid and news.siteid=0)
or (sub.siteid=news.siteid and not news.siteid='0'))
WHERE sub.id IS NOT NULL
order by news.id DESC limit 5
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE subscribe ref suidto,suidwho,siteid suidwho 4 const 2 Using temporary; Using filesort
1 SIMPLE news ALL userid,siteid NULL NULL NULL 88405 Using where
Is there a possible way to use index keys for both userid and siteid?
any time you’ve got an OR in the WHERE clause somewhere, it often helps to split things up into a UNION ALL query
SELECT news.id
, news.userid
FROM news
LEFT OUTER
JOIN subscribe AS sub
ON sub.suidto = news.userid
AND sub.suidwho = '999'
WHERE news.siteid = 0
AND sub.id IS NOT NULL
UNION ALL
SELECT news.id
, news.userid
FROM news
LEFT OUTER
JOIN subscribe AS sub
ON sub.siteid = news.siteid
WHERE news.siteid <> '0'
AND sub.id IS NOT NULL
ORDER
BY id DESC LIMIT 5
just curious, but why are you specifying sub.id IS NOT NULL?? it’s almost as if you want an INNER JOIN instead of LEFT OUTER
Now the speed is around 0.03 seconds after everything.
And about the question, it’s probably just a coding style nothing more, the results are the same. Also can’t see any performance differences, but your question made me think, maybe should test this further.
I need to rewrite and test a lot of queries for performance.
Wish You and People who posted everything best for the new year!