Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: Slow queries.. Cant think of a solution

    The ONLY slow query is
    Code:
    SELECT * FROM video WHERE username = 'XXXXX';
    I need that query because i need to count the rows, therefore be able to count how many videos a user uploaded.

    the member XXXXX has 117738 videos in total...

    Any help?

    PS:
    The server is a private server..
    512 MB RAM and 1GHZ CPU


    Here is the slow queries log

    Code:
    # Time: 091129 19:37:34
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    use lucky_XXXXX;
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:37:45
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:38:06
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:38:42
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 5  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:39:00
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:39:04
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:39:25
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:39:34
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:39:55
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:40:20
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:40:51
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:41:29
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:43:14
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:43:25
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:43:50
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';
    # Time: 091129 19:44:00
    # User@Host: lucky_fb[lucky_fb] @ localhost []
    # Query_time: 4  Lock_time: 0  Rows_sent: 117733  Rows_examined: 117738
    SELECT * FROM video WHERE username = 'XXXXX';

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Try:

    Code:
    SELECT count(*) FROM video WHERE username = 'XXXXX';
    Should be a little bit faster

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is the username column indexed?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2009
    Posts
    3
    Yes, username is indexed.
    select count(*) was actually slower according to response time

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post the full DDL of the table including the indexes. Just because a column is indexed, doesn't mean that your query uses it! Perhaps your column order in the index is not available? Or that you don't have a clustered index?

    etc etc
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2009
    Posts
    3
    Quote Originally Posted by gvee View Post
    Can you post the full DDL of the table including the indexes. Just because a column is indexed, doesn't mean that your query uses it! Perhaps your column order in the index is not available? Or that you don't have a clustered index?

    etc etc
    im new to this.. i dont exactly know what you want me to do

  7. #7
    Join Date
    Nov 2009
    Posts
    21
    Hi George

    Your last post is interesting to me.

    How this could be examined (if the query does not use indexes or its not availiable or not clustered index) ??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •