Explaining SQL Queries

  • evilbeet

    (@evilbeet)


    18 years ago

    Hi all,

    My website has been experiencing a lot of down time lately, and my hosting service says it’s been overwhelmed with SQL queries. So I took a look at the slow queries log.

    I noticed that a lot of the problems came from loading comments — my comments table had nearly 200K rows — so I deleted all comments marked as spam or unapproved, and that brought my comment rows down to around 50K, so hopefully that will help.

    However, there are a few queries in here that I don’t understand at all.

    For instance, I find many that look like this:

    SELECT t.*, tt.*, tr.object_id
    FROM wp_terms
    AS t INNER JOIN wp_term_taxonomy
    AS tt ON tt.term_id = t.term_id
    INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘category’, ‘post_tag’) AND tr.object_id IN (10392, 10387, 10383, 10381, 10378, 10375, 10371, 10369, 10366, 10364, 10362, 10361) ORDER BY t.name ASC;

    This looks complicated and is obviously taking a lot of power, but I have NO idea what it’s doing or why that query would be issued. Any thoughts?

    This one also comes up a lot:

    SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’;

    What is this doing and why is it constantly being issued?

    Lastly, I get some that look like this:

    SELECT comment_date_gmt FROM wp_comments WHERE comment_author_IP = ‘62.85.45.84’ OR comment_author_email = ‘BridgeandT@hotmail.com’ ORDER BY comment_date DESC LIMIT 1;

    The IP and email addresses vary, but the structure is the same. I am not issuing these queries, and I can’t think of anyone someone could issue such a query from my website. Is someone directly querying my database for this info? If so, how and why?

    My website is evilbeetgossip.com

    Any help would be much appreciated!

    Thanks!!!

Viewing 2 replies - 1 through 2 (of 2 total)
  • pinoyca

    (@pinoyca)

    18 years ago

    Query 1. This pulls out the list of ‘terms’ (which are tags and/or categories) of the given post IDs. (See wp_includes/taxonomy.php)

    Query 2. This fetches your home url (as set in “Options | General”), called by get_bloginfo('siteurl') or get_settings('siteurl'). Everybody and their mother calls this whenever they use absolute URLs. (See wp-includes/general-template.php)

    Query 3. WordPress is checking for ‘comment flood’ (see wp-includes/comments.php) each time someone submits a comment. Blame the frequency to your friendly neighbourhood blog spammers.

    (No I had nothing to do with the wordpress source code!)

    I hope this helps!

    Thread Starter evilbeet

    (@evilbeet)

    18 years ago

    Hi Pinoy!

    Thanks so much, that was extremely helpful!

    I’m trying to do some optimization, so I’m wondering about the get_bloginfo() function. It looks like this:

    function get_bloginfo($show = ”, $filter = ‘raw’) {

    switch($show) {
    case ‘url’ :
    case ‘home’ : // DEPRECATED
    case ‘siteurl’ : // DEPRECATED
    $output = get_option(‘home’);
    break;
    case ‘wpurl’ :
    $output = get_option(‘siteurl’);
    break;

    etc….

    Could I save some SQL queries if I went in and manually edited it to look like this:

    function get_bloginfo($show = ”, $filter = ‘raw’) {

    switch($show) {
    case ‘url’ :
    case ‘home’ : // DEPRECATED
    case ‘siteurl’ : // DEPRECATED
    $output = ‘http://mysite.name.com’;
    break;
    case ‘wpurl’ :
    $output = ‘http://mysite.name.com’;
    break;

    I could not find the get_settings() function you mentioned.

    Again, thank you so much for your help, it is very much appreciated!!!

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Explaining SQL Queries’ is closed to new replies.