PHP Optimizing Query Further Slow MySQL Query

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?

Thank you

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.

What’s with the “*,” ?

You think that this is making it slow, just ignore it, I just edit it all out?

  1. I have tried somehow order the results without order by, that turned out impossible.
  2. 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 ).
  3. I don’t know, searching for answers and trying, editing current code, it’s too slow!

Thanks anyways

I minimized the code to this for simplicity:



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?

If you run a “SHOW WARNINGS” after the “EXPLAIN SELECT” in the CLI do you see anything helpful?

https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html

1 Like

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

4 Likes

WOW Thank You So Much For The UNION.

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!

Cheers!

1 Like