WordPress.org

Ready to get started?Download WordPress

Forums

Help required - Slow MySQL queries (1 post)

  1. Rok
    Member
    Posted 5 years ago #

    I've just received following slow queries log, may I request you to help in optimizing the under mentioned queries. Also, I've tried, but couldn't guess, which function is generating these queries?

    ### 1 Query
    ### Total time: 13, Average time: 13
    ### Taking 13 seconds to complete
    ### Rows analyzed 23854
    SELECT /*!XXX SQL_NO_CACHE */ * FROM <code>wp_posts</code>;
    
    SELECT /*!40001 SQL_NO_CACHE */ * FROM <code>wp_posts</code>;
    
    ### 4 Queries
    ### Total time: 9, Average time: 2.25
    ### Taking 2 , 2 , 2 , 3 seconds to complete
    ### Rows analyzed 23543, 23543, 23543 and 23543
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 7, 10;
    
    ### 2 Queries
    ### Total time: 6, Average time: 3
    ### Taking 3 , 3 seconds to complete
    ### Rows analyzed 155567 and 155595
    
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('9', '8015', '7906', '7905', '8006', '196', '319', '7846', '231', '318', '141', '161', '7855', '7843', '30', '8021', '8022', '7857', '7983', '7899', '6136', '7784', '8028', '8031', '7930', '179', '11', '7907', '8017', '7995', '7813', '7372', '7769', '7915', '116', '2613', '7917', '8027', '7778', '7991', '238', '7828', '8032', '7815', '7848', '7786', '7792', '10', '130', '7834', '12', '7884', '7999', '7829', '316', '8020', '8030', '315', '20', '7787', '203', '7933', '7966', '7837', '191', '241', '7962', '184', '267', '7840', '131', '8024', '7909', '7830', '7959', '8009', '7854', '1022', '7978', '38', '8013', '7981', '7842', '8002', '193', '264', '7896', '206', '7994', '7860', '153', '35', '7931', '244', '7984', '7798', '7913', '7971', '7955', '7881', '7919', '79', '7885', '7997', '245', '7993', '283', '36', '1726', '7886', '209', '160', '33', '7956', '7943', '81', '7877', '7773', '8001', '7779', '8003', '211', '289', '240', '210', '7996', '136', '7850', '7951', '7849', '1807', '7895', '7808', '7809', '219', '7937', '7935', '8014', '7949', '7947', '4813', '8010', '7818', '7890', '7807', '6191', '7783', '8012', '7928', '7939', '7986', '7859', '7938', '7825', '8025', '7817', '7950', '7952', '7880', '4812', '7975', '7976', '7889', '7847', '7865', '8007', '7874', '7790', '294', '15', '13', '7914', '334', '338', '7970', '7972', '155', '335', '14', '7973', '16', '317', '1806', '17', '332', '7887', '7802', '7924', '7871', '232', '7891', '19', '167', '166', '18', '187', '328', '1715', '21', '22', '7804', '23', '199', '24', '26', '215', '7863', '27', '204', '28', '29', '213', '7803', '229', '32', '212', '31', '7864', '292', '271', '246', '247', '266', '109', '207', '281', '280', '239', '201', '252', '8018', '8019', '175', '7866', '174', '176', '177', '7845', '233', '234', '230', '223', '185', '7922', '7902', '7819', '7980', '7878', '7872', '1718', '7968', '8008', '1730', '7876', '7942', '7334', '7893', '1716', '8029', '6319', '194', '1720', '1710', '1721', '7960', '7839', '8036', '1712', '1767', '1769', '7888', '7796', '192', '1918', '7875', '339', '309', '1774', '1723', '7879', '1724', '7967', '7908', '228', '7900', '7941', '7827', '7782', '1713', '7904', '1725', '6276', '7954', '6193', '7964', '7374', '1711', '330', '331', '6133', '8026', '7979', '1714', '7882', '6277', '7977', '7989', '7873', '6188', '7797', '7894', '7883', '8005', '1728', '1729', '172', '7892', '343', '7794', '8035', '7982', '7958', '4819', '7990', '6275', '4814', '7869', '7948', '7920', '8016', '7800', '7921', '7868', '7789', '7858', '8004', '7953', '7901', '7856', '224', '7844', '7821', '39', '7897', '7795', '7974', '40', '41', '42', '43', '329', '4817', '4816', '311', '197', '7780', '310', '82', '44', '7814', '7822', '4818', '7870', '7801', '154', '6189', '117', '255', '120', '333', '122', '4477', '284', '119', '138', '110', '336', '121', '111', '139', '218', '112', '7791', '118', '226', '337', '1771', '7862', '1717', '7806', '7824', '1772', '7781', '7775', '216', '34', '113', '7774', '140', '1770', '114', '188', '6187', '7985', '7812', '7912', '7805', '7961', '7810', '7925', '7988', '8011') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 21, 7;
    
    ### 2 Queries
    ### Total time: 4, Average time: 2
    ### Taking 2 , 2 seconds to complete
    ### Rows analyzed 9913 and 11059
    
    SELECT ID, post_title, post_content,MATCH (post_name, post_content) AGAINST ('microsoft roundtable manuals') AS score FROM wp_posts WHERE MATCH (post_name, post_content) AGAINST ('microsoft roundtable manuals') AND post_date <= '2008-10-22 04:57:50' AND (post_status IN ( 'publish', 'static' ) && ID != '11218') AND post_password ='' ORDER BY score DESC LIMIT 10;
    
    ### 2 Queries
    ### Total time: 4, Average time: 2
    ### Taking 2 , 2 seconds to complete
    ### Rows analyzed 155567 and 155595
    
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('9', '8015', '7906', '7905', '8006', '196', '319', '7846', '231', '318', '141', '161', '7855', '7843', '30', '8021', '8022', '7857', '7983', '7899', '6136', '7784', '8028', '8031', '7930', '179', '11', '7907', '8017', '7995', '7813', '7372', '7769', '7915', '116', '2613', '7917', '8027', '7778', '7991', '238', '7828', '8032', '7815', '7848', '7786', '7792', '10', '130', '7834', '12', '7884', '7999', '7829', '316', '8020', '8030', '315', '20', '7787', '203', '7933', '7966', '7837', '191', '241', '7962', '184', '267', '7840', '131', '8024', '7909', '7830', '7959', '8009', '7854', '1022', '7978', '38', '8013', '7981', '7842', '8002', '193', '264', '7896', '206', '7994', '7860', '153', '35', '7931', '244', '7984', '7798', '7913', '7971', '7955', '7881', '7919', '79', '7885', '7997', '245', '7993', '283', '36', '1726', '7886', '209', '160', '33', '7956', '7943', '81', '7877', '7773', '8001', '7779', '8003', '211', '289', '240', '210', '7996', '136', '7850', '7951', '7849', '1807', '7895', '7808', '7809', '219', '7937', '7935', '8014', '7949', '7947', '4813', '8010', '7818', '7890', '7807', '6191', '7783', '8012', '7928', '7939', '7986', '7859', '7938', '7825', '8025', '7817', '7950', '7952', '7880', '4812', '7975', '7976', '7889', '7847', '7865', '8007', '7874', '7790', '294', '15', '13', '7914', '334', '338', '7970', '7972', '155', '335', '14', '7973', '16', '317', '1806', '17', '332', '7887', '7802', '7924', '7871', '232', '7891', '19', '167', '166', '18', '187', '328', '1715', '21', '22', '7804', '23', '199', '24', '26', '215', '7863', '27', '204', '28', '29', '213', '7803', '229', '32', '212', '31', '7864', '292', '271', '246', '247', '266', '109', '207', '281', '280', '239', '201', '252', '8018', '8019', '175', '7866', '174', '176', '177', '7845', '233', '234', '230', '223', '185', '7922', '7902', '7819', '7980', '7878', '7872', '1718', '7968', '8008', '1730', '7876', '7942', '7334', '7893', '1716', '8029', '6319', '194', '1720', '1710', '1721', '7960', '7839', '8036', '1712', '1767', '1769', '7888', '7796', '192', '1918', '7875', '339', '309', '1774', '1723', '7879', '1724', '7967', '7908', '228', '7900', '7941', '7827', '7782', '1713', '7904', '1725', '6276', '7954', '6193', '7964', '7374', '1711', '330', '331', '6133', '8026', '7979', '1714', '7882', '6277', '7977', '7989', '7873', '6188', '7797', '7894', '7883', '8005', '1728', '1729', '172', '7892', '343', '7794', '8035', '7982', '7958', '4819', '7990', '6275', '4814', '7869', '7948', '7920', '8016', '7800', '7921', '7868', '7789', '7858', '8004', '7953', '7901', '7856', '224', '7844', '7821', '39', '7897', '7795', '7974', '40', '41', '42', '43', '329', '4817', '4816', '311', '197', '7780', '310', '82', '44', '7814', '7822', '4818', '7870', '7801', '154', '6189', '117', '255', '120', '333', '122', '4477', '284', '119', '138', '110', '336', '121', '111', '139', '218', '112', '7791', '118', '226', '337', '1771', '7862', '1717', '7806', '7824', '1772', '7781', '7775', '216', '34', '113', '7774', '140', '1770', '114', '188', '6187', '7985', '7812', '7912', '7805', '7961', '7810', '7925', '7988', '8011') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY RAND() DESC LIMIT 0, 5;
    
    ### 1 Query
    ### Total time: 3, Average time: 3
    ### Taking 3 seconds to complete
    ### Rows analyzed 155567
    
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('9', '8015', '7906', '7905', '8006', '196', '319', '7846', '231', '318', '141', '161', '7855', '7843', '30', '8021', '8022', '7857', '7983', '7899', '6136', '7784', '8028', '8031', '7930', '179', '11', '7907', '8017', '7995', '7813', '7372', '7769', '7915', '116', '2613', '7917', '8027', '7778', '7991', '238', '7828', '8032', '7815', '7848', '7786', '7792', '10', '130', '7834', '12', '7884', '7999', '7829', '316', '8020', '8030', '315', '20', '7787', '203', '7933', '7966', '7837', '191', '241', '7962', '184', '267', '7840', '131', '8024', '7909', '7830', '7959', '8009', '7854', '1022', '7978', '38', '8013', '7981', '7842', '8002', '193', '264', '7896', '206', '7994', '7860', '153', '35', '7931', '244', '7984', '7798', '7913', '7971', '7955', '7881', '7919', '79', '7885', '7997', '245', '7993', '283', '36', '1726', '7886', '209', '160', '33', '7956', '7943', '81', '7877', '7773', '8001', '7779', '8003', '211', '289', '240', '210', '7996', '136', '7850', '7951', '7849', '1807', '7895', '7808', '7809', '219', '7937', '7935', '8014', '7949', '7947', '4813', '8010', '7818', '7890', '7807', '6191', '7783', '8012', '7928', '7939', '7986', '7859', '7938', '7825', '8025', '7817', '7950', '7952', '7880', '4812', '7975', '7976', '7889', '7847', '7865', '8007', '7874', '7790', '294', '15', '13', '7914', '334', '338', '7970', '7972', '155', '335', '14', '7973', '16', '317', '1806', '17', '332', '7887', '7802', '7924', '7871', '232', '7891', '19', '167', '166', '18', '187', '328', '1715', '21', '22', '7804', '23', '199', '24', '26', '215', '7863', '27', '204', '28', '29', '213', '7803', '229', '32', '212', '31', '7864', '292', '271', '246', '247', '266', '109', '207', '281', '280', '239', '201', '252', '8018', '8019', '175', '7866', '174', '176', '177', '7845', '233', '234', '230', '223', '185', '7922', '7902', '7819', '7980', '7878', '7872', '1718', '7968', '8008', '1730', '7876', '7942', '7334', '7893', '1716', '8029', '6319', '194', '1720', '1710', '1721', '7960', '7839', '8036', '1712', '1767', '1769', '7888', '7796', '192', '1918', '7875', '339', '309', '1774', '1723', '7879', '1724', '7967', '7908', '228', '7900', '7941', '7827', '7782', '1713', '7904', '1725', '6276', '7954', '6193', '7964', '7374', '1711', '330', '331', '6133', '8026', '7979', '1714', '7882', '6277', '7977', '7989', '7873', '6188', '7797', '7894', '7883', '8005', '1728', '1729', '172', '7892', '343', '7794', '8035', '7982', '7958', '4819', '7990', '6275', '4814', '7869', '7948', '7920', '8016', '7800', '7921', '7868', '7789', '7858', '8004', '7953', '7901', '7856', '224', '7844', '7821', '39', '7897', '7795', '7974', '40', '41', '42', '43', '329', '4817', '4816', '311', '197', '7780', '310', '82', '44', '7814', '7822', '4818', '7870', '7801', '154', '6189', '117', '255', '120', '333', '122', '4477', '284', '119', '138', '110', '336', '121', '111', '139', '218', '112', '7791', '118', '226', '337', '1771', '7862', '1717', '7806', '7824', '1772', '7781', '7775', '216', '34', '113', '7774', '140', '1770', '114', '188', '6187', '7985', '7812', '7912', '7805', '7961', '7810', '7925', '7988', '8011') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 7;
    
    ### 1 Query
    ### Total time: 2, Average time: 2
    ### Taking 2 seconds to complete
    ### Rows analyzed 127253
    
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('131', '7922', '7902', '7819', '7980', '7878', '7872', '1718', '7968', '8008', '1730', '7876', '7942', '7334', '7893', '1716', '8029', '6319', '194', '1720', '1710', '1721', '7960', '7839', '8036', '1712', '1767', '1769', '7888', '7796', '192', '1918', '7875', '339', '309', '1774', '1723', '7879', '1724', '7967', '7908', '228', '7900', '7941', '7827', '7782', '1713', '7904', '1725', '6276', '7954', '6193', '7964', '7374', '1711', '330', '331', '6133', '8026', '7979', '1714', '7882', '6277', '7977', '7989', '7873', '6188', '7797', '7894', '7883', '8005', '1728', '1729', '172', '7892', '343', '7794', '8035', '7982', '7958', '4819', '7990', '6275', '4814', '7869', '7948', '7920', '8016', '7800', '7921', '7868', '7789', '7858', '8004', '7953', '7901') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY RAND() DESC LIMIT 0, 5;
    
    ### 1 Query
    ### Total time: 2, Average time: 2
    ### Taking 2 seconds to complete
    ### Rows analyzed 47075
    
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() DESC LIMIT 0, 5;
    
    ### 1 Query
    ### Total time: 2, Average time: 2
    ### Taking 2 seconds to complete
    ### Rows analyzed 23525
    
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 7, 7;
    
    ### 1 Query
    ### Total time: 2, Average time: 2
    ### Taking 2 seconds to complete
    ### Rows analyzed 25551
    
    SELECT <code>ID</code>, <code>post_author</code>, <code>post_date</code>, <code>post_date_gmt</code>, <code>post_status</code>, <code>post_name</code>, <code>post_modified</code>, <code>post_modified_gmt</code>, <code>post_parent</code>, <code>post_type</code> FROM <code>wp_posts</code> WHERE ( (post_status = 'publish' AND (post_type = 'post' OR post_type = '')) OR (post_status = 'publish' AND post_type = 'page') ) AND post_password='' ORDER BY post_modified DESC LIMIT 0,2000;

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags