wordpress 数据库慢查询优化

问题一

# TIME: 2017-05-07T14:49:44.854500Z
# USER@Host: shipu[shipu] @ localhost []  Id: 417900
# Query_time: 12.077927  Lock_time: 0.000062 Rows_sent: 1  Rows_examined: 433354
USE shipu;
SET TIMESTAMP=1494168584;
SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page', 'attachment') ORDER BY post_modified_gmt DESC LIMIT 1;

explain下看看

MySQL [shipu]> EXPLAIN SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page', 'attachment') ORDER BY post_modified_gmt DESC LIMIT 1;
+----+-------------+----------+------------+------+------------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | TABLE    | partitions | TYPE | possible_keys    | KEY  | key_len | REF  | ROWS   | filtered | Extra                       |
+----+-------------+----------+------------+------+------------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | wp_posts | NULL       | ALL  | type_status_date | NULL | NULL    | NULL | 406713 |    50.00 | USING WHERE; USING filesort |
+----+-------------+----------+------------+------+------------------+------+---------+------+--------+----------+-----------------------------+
1 ROW IN SET, 1 warning (0.08 sec)

fix

CREATE INDEX `modified_gmt_status_type` ON `wp_posts`(`post_modified_gmt`, `post_status`, `post_type`);
MySQL [shipu]> CREATE INDEX `modified_gmt_status_type` ON `wp_posts`(`post_modified_gmt`, `post_status`, `post_type`);
Query OK, 0 ROWS affected (4.49 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MySQL [shipu]> EXPLAIN SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page', 'attachment') ORDER BY post_modified_gmt DESC LIMIT 1;
+----+-------------+----------+------------+-------+------------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | TABLE    | partitions | TYPE  | possible_keys    | KEY                      | key_len | REF  | ROWS | filtered | Extra                    |
+----+-------------+----------+------------+-------+------------------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | wp_posts | NULL       | INDEX | type_status_date | modified_gmt_status_type | 169     | NULL |    1 |    50.00 | USING WHERE; USING INDEX |
+----+-------------+----------+------------+-------+------------------+--------------------------+---------+------+------+----------+--------------------------+
1 ROW IN SET, 1 warning (0.00 sec)

问题二

某些主题作者为了抓取文章内图片写了以下代码

function hui_get_attachment_id_from_src ($link) {
    global $wpdb;
    $link = preg_replace('/-\d+x\d+(?=\.(jpg|jpeg|png|gif)$)/i', '', $link);
    return $wpdb->get_var("SELECT ID FROM {$wpdb->posts} WHERE guid='$link'");
}

在文章量小的时候问题不大,当达到数万条文章时,检索数据库会长达10几秒,为此对guid增加索引

ALTER TABLE `wp_posts` ADD INDEX(`guid`);

还没有评论,快来抢沙发!

发表评论

  • 😉
  • 😐
  • 😡
  • 😈
  • 🙂
  • 😯
  • 🙁
  • 🙄
  • 😛
  • 😳
  • 😮
  • emoji-mrgree
  • 😆
  • 💡
  • 😀
  • 👿
  • 😥
  • 😎
  • ➡
  • 😕
  • ❓
  • ❗
  • 70 queries in 0.401 seconds