Forum Slow Queries

محروم.كوم · #1 11-03-2012, 07:20 AM

Can anyone be of help? I need to know if I can improve these queries somehow. Are there indices I should add?

Thanks in advance,

-Jason

Code:
# Time: 121102 2348
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 49.773881 Lock_time: 0.000074 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911768;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29650
AND T.dateline > 315532800 AND T.dateline < 1351911718
AND P.dateline > 315532800 AND P.dateline < 1351911718
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 2314
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 48.809440 Lock_time: 0.000040 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911794;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29650
AND T.dateline > 315532800 AND T.dateline < 1351911745
AND P.dateline > 315532800 AND P.dateline < 1351911745
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 2340
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 50.111861 Lock_time: 0.000037 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911820;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29651
AND T.dateline > 315532800 AND T.dateline < 1351911770
AND P.dateline > 315532800 AND P.dateline < 1351911770
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 2346
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 3.758290 Lock_time: 0.000035 Rows_sent: 0 Rows_examined: 1
SET timestamp=1351911826;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29652
AND T.dateline > 315532800 AND T.dateline < 1351911822
AND P.dateline > 315532800 AND P.dateline < 1351911822
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 23:04:09
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 52.756725 Lock_time: 0.000042 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911849;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29651
AND T.dateline > 315532800 AND T.dateline < 1351911796
AND P.dateline > 315532800 AND P.dateline < 1351911796
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 23:04:14
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 3.492491 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 1
SET timestamp=1351911854;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29652
AND T.dateline > 315532800 AND T.dateline < 1351911851
AND P.dateline > 315532800 AND P.dateline < 1351911851
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 23:04:39
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 50.836806 Lock_time: 0.000039 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911879;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29653
AND T.dateline > 315532800 AND T.dateline < 1351911828
AND P.dateline > 315532800 AND P.dateline < 1351911828
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 23:05:06
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 49.081818 Lock_time: 0.000040 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911906;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29653
AND T.dateline > 315532800 AND T.dateline < 1351911856

AND P.dateline > 315532800 AND P.dateline < 1351911856
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 23:05:30
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 49.090029 Lock_time: 0.000037 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911930;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29654
AND T.dateline > 315532800 AND T.dateline < 1351911881
AND P.dateline > 315532800 AND P.dateline < 1351911881
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 23:05:59
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 51.203447 Lock_time: 0.000055 Rows_sent: 1 Rows_examined: 3495990

SET timestamp=1351911959;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29654
AND T.dateline > 315532800 AND T.dateline < 1351911908
AND P.dateline > 315532800 AND P.dateline < 1351911908
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

# Time: 121102 23:06:30
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 48.364820 Lock_time: 0.000047 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351911990;
SELECT
P.userid, COUNT(userid) AS times
FROM vb_thread AS T
INNER JOIN vb_post AS P ON T.lastpostid=P.postid
WHERE P.userid=29657
AND T.dateline > 315532800 AND T.dateline < 1351911942
AND P.dateline > 315532800 AND P.dateline < 1351911942
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid; __DEFINE_LIKE_SHARE__

المواضيع المتشابهه
الموضوع	كاتب الموضوع	المنتدى	مشاركات	آخر مشاركة
Forum Need two queries please	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	06-24-2010 03:10 AM
"List New Registrations" in admincp causes slow queries.	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	03-17-2010 09:40 PM
CMS Have the CMS slow queries been fixed?	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	02-20-2010 05:40 AM
Slow Queries forumdisplay	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	06-21-2009 12:00 AM
Slow queries.	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	04-27-2009 10:50 PM