Slow Queries

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • trackpads
    Senior Member
    • Aug 2003
    • 486
    #1

    [Forum] Slow Queries

    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 23:02:48
# 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 23:03:14
# 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 23:03:40
# 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 23:03:46
# 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;
    sigpic
    Tags: None
    • Hartmut
      Senior Member
      • Nov 2007
      • 2870
      • 4.2.x
      #2
      I don't believe that you can do much about it, the queries actually look good and compact.
      No private support, only PM me when I ask for it. Support in the forums only.

        Comment

        • trackpads
          Senior Member
          • Aug 2003
          • 486
          #3
          I agree on the queries, would an index help or anything? there are people with larger sites and I cant imagine they have 50 sec + queries

          - - - Updated - - -

          Here is a doosey:

          Code:
          # Time: 121102 22:38:02
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 207.584678 Lock_time: 0.000028 Rows_sent: 1 Rows_examined: 3495990
SET timestamp=1351910282;
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=29585
AND T.dateline > 315532800 AND T.dateline < 1351910074
AND P.dateline > 315532800 AND P.dateline < 1351910074
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;
          - - - Updated - - -

          It ended up being VB Experience, thanks to all,

          -Jason
          sigpic

            Comment

            Previous template Next
            widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
            Working...