Я бы немного помог в оптимизации этого запроса wordpress, он в настоящее время занимает 100% использования процессора и никогда не имел возможности для его завершения:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id) INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id) INNER JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id) INNER JOIN wp_postmeta AS mt7 ON (wp_posts.ID = mt7.post_id) INNER JOIN wp_postmeta AS mt8 ON (wp_posts.ID = mt8.post_id) INNER JOIN wp_postmeta AS mt9 ON (wp_posts.ID = mt9.post_id) INNER JOIN wp_postmeta AS mt10 ON (wp_posts.ID = mt10.post_id) WHERE 1=1 AND wp_posts.post_type = 'produkter' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'csv_product_month_sub' OR (mt1.meta_key = 'csv_product_type' AND CAST(mt1.meta_value AS CHAR) = 'Mobilt Bredbaand') OR (mt2.meta_key = 'csv_product_consumption' AND CAST(mt2.meta_value AS SIGNED) BETWEEN '0' AND '2') OR (mt3.meta_key = 'csv_product_consumption' AND CAST(mt3.meta_value AS SIGNED) BETWEEN '3' AND '9') OR (mt4.meta_key = 'csv_product_consumption' AND CAST(mt4.meta_value AS SIGNED) BETWEEN '10' AND '19') OR (mt5.meta_key = 'csv_product_download' AND CAST(mt5.meta_value AS SIGNED) BETWEEN '2' AND '9') OR (mt6.meta_key = 'csv_product_download' AND CAST(mt6.meta_value AS SIGNED) BETWEEN '10' AND '19') OR (mt7.meta_key = 'csv_product_download' AND CAST(mt7.meta_value AS SIGNED) BETWEEN '20' AND '29') OR (mt8.meta_key = 'csv_product_month_sub' AND CAST(mt8.meta_value AS SIGNED) BETWEEN '0' AND '49') OR (mt9.meta_key = 'csv_product_month_sub' AND CAST(mt9.meta_value AS SIGNED) BETWEEN '50' AND '99') OR (mt10.meta_key = 'csv_product_month_sub' AND CAST(mt10.meta_value AS SIGNED) BETWEEN '100' AND '149') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC Пока у меня есть:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE ID IN ( SELECT post_id from wp_postmeta WHERE ( (meta_key = 'csv_product_consumption' AND CAST(meta_value AS SIGNED) BETWEEN 0 AND 2) OR (meta_key = 'csv_product_consumption' AND CAST(meta_value AS SIGNED) BETWEEN 3 AND 9) OR (meta_key = 'csv_product_consumption' AND CAST(meta_value AS SIGNED) BETWEEN 10 AND 19) OR (meta_key = 'csv_product_download' AND CAST(meta_value AS SIGNED) BETWEEN 2 AND 9) OR (meta_key = 'csv_product_download' AND CAST(meta_value AS SIGNED) BETWEEN 10 AND 19) OR (meta_key = 'csv_product_download' AND CAST(meta_value AS SIGNED) BETWEEN 20 AND 29) OR (meta_key = 'csv_product_month_sub' AND CAST(meta_value AS SIGNED) BETWEEN 0 AND 49) OR (meta_key = 'csv_product_month_sub' AND CAST(meta_value AS SIGNED) BETWEEN 50 AND 99) OR (meta_key = 'csv_product_month_sub' AND CAST(meta_value AS SIGNED) BETWEEN 100 AND 149) ) GROUP BY post_id ) AND wp_posts.post_type = 'produkter' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( (wp_postmeta.meta_key = 'csv_product_type') AND (wp_postmeta.meta_value = 'Mobilt Bredbaand')) ORDER BY wp_postmeta.meta_value+0 ASC
Он больше не использует слишком много CPU, и запрос занимает 0.0331 сек.
Любые другие идеи будут оценены.
Я столкнулся с этой проблемой и понял, что проблема связана со всеми INNER JOINS, созданными WordPress. Я получил необработанный запрос из WordPress:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id) INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (7,11,12,13,14,15) AND wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (10) ) ) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product', 'store_page') AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'product_type' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') OR (mt1.meta_key = 'product_type2' AND CAST(mt1.meta_value AS CHAR) = 'type_pre_ground') OR (mt2.meta_key = 'product_type3' AND CAST(mt2.meta_value AS CHAR) = 'type_pre_ground') OR (mt3.meta_key = 'product_type4' AND CAST(mt3.meta_value AS CHAR) = 'type_pre_ground') OR (mt4.meta_key = 'product_type5' AND CAST(mt4.meta_value AS CHAR) = 'type_pre_ground') OR (mt5.meta_key = 'product_type6' AND CAST(mt5.meta_value AS CHAR) = 'type_pre_ground') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
Это никогда не завершалось и не связывало процессор с очень высокой нагрузкой. Я удалил последние два INNER JOINs (и соответствующие предложения WHERE) и получил результаты за 2 секунды:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (7,11,12,13,14,15) AND wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (10) ) ) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product', 'store_page') AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'product_type' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') OR (mt1.meta_key = 'product_type2' AND CAST(mt1.meta_value AS CHAR) = 'type_pre_ground') OR (mt2.meta_key = 'product_type3' AND CAST(mt2.meta_value AS CHAR) = 'type_pre_ground') OR (mt3.meta_key = 'product_type4' AND CAST(mt3.meta_value AS CHAR) = 'type_pre_ground') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
Поэтому я понял, что удаление INNER JOINS стало ключом к ускорению запроса. Я переписал запрос только одним wp_postmeta INNER JOIN и получил результаты в доли секунды:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (7,11,12,13,14,15) AND wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (10) ) ) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product', 'store_page') AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'product_type' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') OR (wp_postmeta.meta_key = 'product_type2' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') OR (wp_postmeta.meta_key = 'product_type3' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') OR (wp_postmeta.meta_key = 'product_type4' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') OR (wp_postmeta.meta_key = 'product_type5' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') OR (wp_postmeta.meta_key = 'product_type6' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
С помощью этого решения я создал фильтр WordPress для преобразования запроса postmeta в более быстрый формат:
function custom_get_meta_sql( $meta_sql ) { global $wpdb; $posts_table = $wpdb->prefix . 'posts'; $postmeta_table = $wpdb->prefix . 'postmeta'; //use single INNER JOIN $meta_sql['join'] = " INNER JOIN {$postmeta_table} AS pmta ON ({$posts_table}.ID = pmta.post_id) "; //replace the mtNN aliases with wp_postmeta $where_clause = $meta_sql['where']; $where_clause = str_replace("{$postmeta_table}.", 'pmta.', $where_clause); $where_clause = preg_replace('/mt\d+\.meta_/i', 'pmta.meta_', $where_clause); $meta_sql['where'] = $where_clause; return $meta_sql; } add_filter( 'get_meta_sql', 'custom_get_meta_sql' );
Этот фильтр должен быть расположен в файле functions.php вашей темы WordPress.
Это старый пост сейчас, но вы можете попробовать попробовать Horizontal Meta. Горизонтальный метаконтролирует определенные ключи в мета-таблицах post & user и извлекает их в реляционный / горизонтальный формат. Вы можете использовать механизм запросов WordPress для запуска запросов, но Horizontal Meta переписывает запросы, чтобы ускорить его. Доступно здесь: http://wordpress.org/plugins/horizontal-meta/