Performance issue of History plugin?

Question

I am using NoahY's history plugin which is great: https://github.com/NoahY/q2a-history

However, I have got a performance issue. Try to load this site: http://www.gute-mathe-fragen.de/user/echteinfachtv?tab=history It takes up to 5 seconds to appear.

Others q2a-sites on my domain load below 1 second.

Could the php code (or mysql?) be improved to speed up the history?

Thank you!

 

----

Update 2012-10-17 12:07h

the delay is caused by function user_activity_form().
If you comment it out at line 101: $content['form-activity-list'] = $this->user_activity_form();  
the page loads immediately.

I thought it could be the while loop on line 224:
        while ( ($event=qa_db_read_one_assoc($events,true)) !== null ) {
but setting a continue right in the beginning does *not* speed up things!

Setting a return in the function user_activity_form() on different positions I could see the the MySQL query on line 145 takes ages:

        $events = qa_db_query_sub(
            "SELECT
                e.event,
                BINARY e.params as params,
                UNIX_TIMESTAMP(e.datetime) AS datetime,
                p.postid AS postid
            FROM
                ^eventlog AS e
            LEFT JOIN
                ^posts AS p
                ON e.params LIKE CONCAT('%postid=', p.postid, '\t%' ) OR e.params LIKE CONCAT('%postid=', p.postid)
            WHERE
                e.userid=#
                AND
                DATE_SUB(CURDATE(),INTERVAL # DAY) <= datetime
            ORDER BY datetime DESC"
            .(qa_opt('user_act_list_max')?" LIMIT ".(int)qa_opt('user_act_list_max'):""),
            $userid, qa_opt('user_act_list_age')
        );

This is the guilty one ;)
 

Comments

It is probably the JOIN clause. Using LIKE for that is pretty inefficient, you should join on an actual field e.g. postid. I'm guessing the eventlog table doesn't have a postid field, which it probably should.

---

Hi Scott, thanks for the input. Table qa_eventlog has columns: datetime, ipaddress, userid, handle, cookieid, event, params.

I see myself still amateur using mysql, and will look into it soon, trying to find a solution. Of course I hope that NoahY might have an idea on that :)

Answer 1

Mmm.  Trying to do too much with one query I guess.  Can you try version 1.6 and see if it works?

Comments

Good morning Noah, I just checked the recent version that you upload 50 min ago. Very nice, working fast and showing all* events.

Not showing these events: a_delete, c_delete, q_delete (but I think this has to do with deleted questions and skipping them, which is okay!).

One badge is not showing up in my list, another one is. Maybe this has to do with my badge-plugin version 2.2?

---

I changed line 256 from:
if(!in_array($type, $nopost) && $post == null)

to:
if(!in_array($type, $nopost) && $post == null && $type!='badge_awarded')

Now all badges are showing up!

PS: Thank you Noah, your badge: "best q2a-plugin developer"!

---

Last thing to mention: I changed badge awarding, rechecked, new badges got awarded. However, no notification and nothing showing up in the user history list? ... I will update the badges plugin to the newest version soon, as I think this might be the problem. Just wanted to let you know.

---

Hi Noah, I am not sure but could the new amount of queries of the plugin be part of this error (that I got yesterday night): "has more than 'max_user_connections' active connections in /qa-include/qa-db.php on line 66" → see here http://question2answer.org/qa/18513/ ?