Performance debugging for question list query (modified by plugin)

Question

I'm adding the below 2 queries where the only difference between them is the part "AND qa_userfavorites.entitytype='Q'"

PS: the below queries are coming from a Q2A installation with a custom plugin that adds the part "left join  qa_userreads on qa_userreads.userid = 3 and qa_posts.postid = qa_userreads.postid"

SELECT qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type, 1) AS `basetype`, INSTR(qa_posts.type, '_HIDDEN')>0 AS `hidden`, INSTR(qa_posts.type, '_QUEUED')>0 AS `queued`, qa_posts.acount, qa_posts.amaxvote, qa_posts.selchildid, qa_posts.closedbyid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, qa_posts.title, qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created) AS `created`, qa_posts.name, qa_categories.title AS `categoryname`, qa_categories.backpath AS `categorybackpath`, CONCAT_WS(',', qa_posts.catidpath1, qa_posts.catidpath2, qa_posts.catidpath3,
qa_posts.categoryid) AS `categoryids`, qa_uservotes.vote AS `uservote`, qa_uservotes.flag AS `userflag`, qa_userfavorites.entityid<=>qa_posts.postid AS `userfavoriteq`, qa_posts.userid, qa_posts.cookieid, qa_posts.createip AS `createip`, qa_userpoints.points, qa_users.flags, qa_users.level, qa_users.email AS `email`, qa_users.handle AS `handle`, BINARY qa_users.avatarblobid AS `avatarblobid`, qa_users.avatarwidth, qa_users.avatarheight, qa_userreads.postid <=> qa_posts.postid as `userread` FROM qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_uservotes ON qa_posts.postid=qa_uservotes.postid AND qa_uservotes.userid='3' LEFT JOIN qa_userfavorites ON qa_posts.postid=qa_userfavorites.entityid AND qa_userfavorites.userid="1"  LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid left join  qa_userreads on qa_userreads.userid ="1" and qa_posts.postid = qa_userreads.postid   JOIN (SELECT postid FROM qa_posts WHERE type='Q' ) y ON qa_posts.postid=y.postid where qa_posts.type='Q' ORDER BY qa_posts.created DESC LIMIT 0,20;
20 rows in set, 1 warning (0.00 sec)

SELECT qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type, 1) AS `basetype`, INSTR(qa_posts.type, '_HIDDEN')>0 AS `hidden`, INSTR(qa_posts.type, '_QUEUED')>0 AS `queued`, qa_posts.acount, qa_posts.amaxvote, qa_posts.selchildid, qa_posts.closedbyid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, qa_posts.title, qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created) AS `created`, qa_posts.name, qa_categories.title AS `categoryname`, qa_categories.backpath AS `categorybackpath`, CONCAT_WS(',', qa_posts.catidpath1, qa_posts.catidpath2, qa_posts.catidpath3,
qa_posts.categoryid) AS `categoryids`, qa_uservotes.vote AS `uservote`, qa_uservotes.flag AS `userflag`, qa_userfavorites.entityid<=>qa_posts.postid AS `userfavoriteq`, qa_posts.userid, qa_posts.cookieid, qa_posts.createip AS `createip`, qa_userpoints.points, qa_users.flags, qa_users.level, qa_users.email AS `email`, qa_users.handle AS `handle`, BINARY qa_users.avatarblobid AS `avatarblobid`, qa_users.avatarwidth, qa_users.avatarheight, qa_userreads.postid <=> qa_posts.postid as `userread` FROM qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_uservotes ON qa_posts.postid=qa_uservotes.postid AND qa_uservotes.userid='3' LEFT JOIN qa_userfavorites ON qa_posts.postid=qa_userfavorites.entityid AND qa_userfavorites.userid="1" AND qa_userfavorites.entitytype='Q' LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid left join  qa_userreads on qa_userreads.userid = "1" and qa_posts.postid = qa_userreads.postid   JOIN (SELECT postid FROM qa_posts WHERE type='Q' ) y ON qa_posts.postid=y.postid where qa_posts.type='Q'
ORDER BY qa_posts.created DESC LIMIT 0,20;
20 rows in set, 1 warning (1.34 sec)

The performance difference is startling and I'm puzzled why this is so. If I remove the custom plugin this issue is not there.

This performance issue can be fixed by modifying the below line but just wondering why this is happening in the first place.
https://github.com/q2a/question2answer/blob/dev/qa-include/db/selects.php#L133

Comments

1. There is an unneeded JOIN: SELECT postid FROM qa_posts WHERE type='Q'
2. It is a bit unclear why you're comparing those two queries. They're conceptually different, even though in certain situations, the returned results might be the same
3. Check if forcing the "userid" index when joining userfavorites improves query time
4. What are the warnings you're receiving?
5. Can you confirm you're dodging the query cache with SQL_NO_CACHE when timing the queries?

---

Thank you @pupi1985 for your reply.

1. Yes, this being a "questions" page that join is added by "qa_db_qs_selectspec" function.
Actually removing this join recovers the performance loss. I had made a change here to the Q2A core so that post filtering works with pagination.
(//              "type=$ " . $sortsql . " LIMIT #,#) y ON ^posts.postid=y.postid";
                        "type=$ ) y ON ^posts.postid=y.postid where ^posts.type='$type' ".$sortsql." LIMIT #,#";)
2. Actually I'm having only the low performing query. The other one was derived as part of performance debugging. I got it that they are not equal.
3. 'userfavorites' table is not having userid index right?
4. mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                      |
+---------+------+----------------------------------------------------------------------------------------------+
| Warning | 1287 | 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead |
+---------+------+----------------------------------------------------------------------------------------------+
5. MySQL 8 is not having Query cache right?

---

1. Interesting. So the performance went down to a number similar to the faster query?
3. It does. It is defined as "KEY `userid` (`userid`,`nouserevents`)," (Don't look at me, I didn't name it that way :) )
4. OK, it seems things are getting deprecated in MySQL as well.
5. Query cache has been disabled in MySQL 8

---

1. Yes it did.
3. Thank you @pupi1985 :) That did it. Performance became as good as the faster one.

Answer 1

Slow JOIN operations are typically caused by missing indexes. Analyze the slow query with EXPLAIN to identify the problematic part:

EXPLAIN SELECT qa_posts.postid, qa_posts.categoryid, ...

Comments

Thank you Ansgar. Actually qa_userreads table is having similar index structure like qa_userfavorites. Not exactly getting how indexes are making a difference here. These are the 2 results with EXPLAIN:

Good one: https://drive.google.com/file/d/1fQMn-9LQJmhl2kssPG55RGeZuiiIB_hs/view?usp=sharing

Bad one: https://drive.google.com/file/d/1_VGPJoBTd6tMuZ-ArmXyK8f7LtcL1foX/view?usp=sharing