Help
General ArrowChat talk that doesn't fit any other forum.
User avatar
ISushko
Customer
 
Posts: 1
Joined: 12 Oct 2014, 01:03

 

by ISushko 16 Jan 2017, 19:01

In version 1.8 a ton of the mysql queries were written incorrectly and were causing our website, which serves over a million visitors and has a database over nearly 800,000 members to constantly crash.
Have these queries been re-written in 2.0?

For example this does a full table scan each time:
SELECT DISTINCT arrowchat_status.userid, arrowchat_status.session_time lastactivity, arrowchat_status.status, arrowchat_status.guest_name, arrowchat_status.is_admin
FROM arrowchat_status
WHERE (arrowchat_status.session_time + 60 + 120) > 1421956248
AND LEFT(arrowchat_status.userid, 1) = 'g'
ORDER BY arrowchat_status.userid ASC;

Should be:

SELECT DISTINCT user.userid userid, user.username username, arrowchat_status.session_time lastactivity, user.userid avatar, user.userid link, arrowchat_status.is_admin, arrowchat_status.status FROM user,arrowchat_status where user.userid = arrowchat_status.userid and arrowchat_status.session_time > 120+60+1422341740 AND user.userid != '353039' ORDER BY user.username ASC;

Another example that took over 1 second to execute:
SELECT user.userid userid, user.username username, arrowchat_status.session_time lastactivity, user.userid link, user.userid avatar, arrowchat_status.is_admin, arrowchat_status.status
FROM user
LEFT JOIN arrowchat_status
ON user.userid = arrowchat_status.userid
WHERE user.userid = '623919';

Re-written, it executes instantly:
SELECT user.userid userid, user.username username, arrowchat_status.session_time lastactivity, user.userid link, user.userid avatar, arrowchat_status.is_admin, arrowchat_status.status FROM user,arrowchat_status where user.userid = arrowchat_status.userid and arrowchat_status.userid = '623919';
User avatar
Jason
Customer
 
Posts: 2329
Joined: 12 Dec 2009, 16:06

 

by Jason 17 Jan 2017, 14:20

The specific queries you referenced were indeed changed.