Professional Web Applications Themes

Troubleshooting MySQL. - MySQL

Hi everyone. I have recently moved over to a new webhost, who seem to be very good, but I am experiencing big time differences in the times that my SQL queries are executed. This is understandable when you consider I have a lot of other people sharing the same server, however, What is odd, is that the delay only becomes serious when I use nested queries. This is the query I am executing. SELECT SQL_CALC_FOUND_ROWS is_retired, ub.is_owned, b.book_id AS bid, b.book_name AS name FROM pp_books b LEFT JOIN ( SELECT IF(username IS NULL,username,'1') AS is_owned, book_id, user_id, uid FROM pp_ubooks, ...

  1. #1

    Default Troubleshooting MySQL.

    Hi everyone. I have recently moved over to a new webhost, who seem to
    be very good, but I am experiencing big time differences in the times
    that my SQL queries are executed.

    This is understandable when you consider I have a lot of other people
    sharing the same server, however, What is odd, is that the delay only
    becomes serious when I use nested queries.

    This is the query I am executing.

    SELECT SQL_CALC_FOUND_ROWS is_retired, ub.is_owned, b.book_id AS bid,
    b.book_name AS name FROM pp_books b LEFT JOIN ( SELECT IF(username IS
    NULL,username,'1') AS is_owned, book_id, user_id, uid FROM pp_ubooks,
    cms_users WHERE uid=user_id AND username='kazzy4eva' OR username IS
    NULL ) AS ub ON ub.book_id=b.book_id ORDER BY name;

    On my local machine, I get:
    3672 rows in set (0.00 sec)
    No problems here.

    However, on my new webhost's server, I get:
    3672 rows in set (2.96 sec)
    Now this is a big difference as you can see.

    After playing with the query and breaking it down, here are the
    results:

    When I do:
    SELECT SQL_CALC_FOUND_ROWS is_retired, ub.is_owned, b.book_id AS bid,
    b.book_name AS name FROM pp_books b;
    on it's own (on my new webhost's server), I get:
    3672 rows in set (0.01 sec)
    This isn't bad at all.

    When I do the subselect:
    SELECT IF(username IS NULL,username,'1') AS is_owned, book_id, user_id,
    uid FROM pp_ubooks, cms_users WHERE uid=user_id AND
    username='kazzy4eva' OR username IS NULL
    on it's own (on my new webhost's server), I get:
    3007 rows in set (0.02 sec)

    So where do those extra seconds come from when I execute the query
    using subselects? Would I be right in assuming that this could be an
    issue with available RAM?

    I don't think there is anything I can do to fix this problem, however I
    am sure you'll agree, it is bizzare. I would appreciate any input that
    I might be able to quote to my webhost, that may help with fuxing the
    problem. We are both running the same version of MySQL, too, so that
    shouldn't be an issue.

    Many thanks in advance.

    Daz.

    Daz Guest

  2. #2

    Default Re: Troubleshooting MySQL.

    Daz wrote: 

    Try

    EXPLAIN SELECT ...
     


    --
    File not found: (R)esume, (R)etry, (R)erun, (R)eturn, (R)eboot
    Pedro Guest

  3. #3

    Default Re: Troubleshooting MySQL.


    Daz wrote: 

    I've said it many times and I will say it again "Avoid subselects like
    the plague".

    Change your subselect into 2 separate LEFT JOINS using the WHERE
    clauses as the join conditions. You will be amazed at the performance
    improvement.

    Captain Guest

Similar Threads

  1. VOD Troubleshooting
    By raydaly4 in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: March 27th, 02:00 PM
  2. troubleshooting
    By cosmic007 in forum Macromedia Flash Flashcom
    Replies: 6
    Last Post: November 2nd, 09:42 AM
  3. troubleshooting help please?
    By Tal in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 27th, 07:23 PM
  4. [Q] 2k->xp troubleshooting
    By zzZIng in forum Windows Networking
    Replies: 0
    Last Post: July 18th, 07:44 PM
  5. troubleshooting-help me please
    By Ray in forum Adobe Photoshop Elements
    Replies: 6
    Last Post: July 4th, 04:21 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139