Results 1 to 7 of 7
  1. #1

    Optimizing mysql

    These two queries are taking so much load in the server. Always the status is

    "Copying to tmp table".

    Any idea how to optimize this


    $lastpostmodified = $wpdb->get_var("SELECT post_modified_gmt FROM $wpdb->posts WHERE post_mo
    dified_gmt <= '$now' AND post_status = 'publish' ORDER BY post_modified_gmt DESC LIMIT 1");
    break;
    case 'blog':
    $lastpostmodified = $wpdb->get_var("SELECT post_modified FROM $wpdb->posts WHERE post_modifi
    ed_gmt <= '$now' AND post_status = 'publish' ORDER BY post_modified_gmt DESC LIMIT 1");

  2. #2
    Join Date
    Sep 2004
    Location
    Flint, Michigan
    Posts
    5,765
    Well that status means that your temporary table created by the script is larger than what you have set for MySQL and it has to copy the table to the disk.

    How many rows are you searching through for that actual query? It looks like it's pretty decent other than the <= $now (that's what is eating up so much space most likely). You could also optomize the table within phpMyAdmin and/or consider creating an index on the post_modified_gmt column.
    Mike from Zoodia.com
    Professional web design and development services.
    In need of a fresh hosting design? See what premade designs we have in stock!
    Web design tips, tricks, and more at MichaelPruitt.com

  3. #3
    I can help you out with this, please do a:


    show create table <TABLENAME>

    Then do a:

    EXPLAIN SQLQUERY \G


    paste the output here and we can work from here.

  4. #4
    wp_posts |CREATE TABLE `wp_posts` (
    `ID` bigint(20) unsigned NOT NULL auto_increment,
    `post_author` int(4) NOT NULL default '0',
    `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
    `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
    `post_content` longtext NOT NULL,
    `post_title` text NOT NULL,
    `post_category` int(4) NOT NULL default '0',
    `post_excerpt` text NOT NULL,
    `post_status` enum('publish','draft','private','static','object') NOT NULL default 'publish',
    `comment_status` enum('open','closed','registered_only') NOT NULL default 'open',
    `ping_status` enum('open','closed') NOT NULL default 'open',
    `post_password` varchar(20) NOT NULL default '',
    `post_name` varchar(200) NOT NULL default '',
    `to_ping` text NOT NULL,
    `pinged` text NOT NULL,
    `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
    `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
    `post_content_filtered` text NOT NULL,
    `post_parent` int(11) NOT NULL default '0',
    `guid` varchar(255) NOT NULL default '',
    `menu_order` int(11) NOT NULL default '0',
    PRIMARY KEY (`ID`),
    KEY `post_name` (`post_name`),
    KEY `post_status` (`post_status`),
    KEY `post_modified_gmt` (`post_modified_gmt`)
    ) TYPE=MyISAM |


    mysql> EXPLAIN SELECT post_date_gmt FROM wp_posts WHERE post_date_gmt <= '2005-07-25 11:14:47' AND post_status = 'publish' ORDER BY post_date_gmt DESC LIMIT 1;
    +----------+-------+---------------+-------------+---------+------+-------+-----------------------------+
    | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----------+-------+---------------+-------------+---------+------+-------+-----------------------------+
    | wp_posts | range | post_status | post_status | 1 | NULL | 48587 | Using where; Using filesort |
    +----------+-------+---------------+-------------+---------+------+-------+-----------------------------+
    1 row in set (0.00 sec)



    These are the results of what you specified. Thanks for replying

  5. #5
    Actually this one also takes load

    EXPLAIN SELECT DISTINCT * FROM wp_posts WHERE 1=1 AND post_date_gmt <= '2005-07-25 11:26:59' AND (post_status = "publish") GROUP BY wp_posts.ID ORDER BY post_date DESC LIMIT 0, 100;
    +----------+-------+---------------+-------------+---------+------+-------+----------------------------------------------+
    | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----------+-------+---------------+-------------+---------+------+-------+----------------------------------------------+
    | wp_posts | range | post_status | post_status | 1 | NULL | 48587 | Using where; Using temporary; Using filesort |
    +----------+-------+-------------


    The status will be always copying to tmp table

  6. #6
    Join Date
    Jan 2003
    Posts
    1,715
    You have 4 different time fields going here, and only one is indexed. You may want to store all dates as GMT and make the offset a user-preference.

    For now, pick either the _date or _date_gmt variants, index them, and use them consistently. You may also consider dropping the status index, which may trick it into not using the LIMIT clause.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  7. #7
    Join Date
    Jul 2005
    Posts
    256
    For the first query you need to make MySQL use range index scan instead of key lookups series.
    First, do
    OPTIMIZE TABLE wp_posts;
    and try your query. If it works, you should get the result instantly. If not, change your query to:
    $lastpostmodified = $wpdb->get_var("SELECT post_modified_gmt FROM $wpdb->posts FORCE INDEX post_modified_gmt WHERE post_mo
    dified_gmt <= '$now' AND post_status = 'publish' ORDER BY post_modified_gmt DESC LIMIT 1");

    Second query is a bit more tricky. What you want is to make MySQL do loose or tight index scan instead of table scan.
    Better way of accomplishing it is changing
    ORDER BY post_date
    to
    ORDER BY post_date_gmt
    and adding compound index:
    ALTER TABLE wp_posts ADD INDEX (ID, post_date_gmt, post_status);
    Somewhat worse (but still working) way is to just add bigger compound index without changing the query:
    ALTER TABLE wp_posts ADD INDEX (ID, post_date, post_date_gmt, post_status);

Posting Permissions

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