hi,
i have word press blog running with mysql. select query on posting table wpmb_posts always takes more than 70 seconds. How can i improve the performance of this query.
Here is query:
SELECT DISTINCT SQL_CALC_FOUND_ROWS wpmb_posts.* FROM wpmb_posts LEFT JOIN wpmb_term_relationships AS trel ON (wpmb_posts.ID = trel.object_id) LEFT JOIN wpmb_term_taxonomy AS ttax ON ( ( ttax.taxonomy = ‘category’ OR ttax.taxonomy = ‘post_tag’ OR ttax.taxonomy = ‘post_format’ OR ttax.taxonomy = ‘primary_category’ ) AND trel.term_taxonomy_id = ttax.term_taxonomy_id) LEFT JOIN wpmb_terms AS tter ON (ttax.term_id = tter.term_id) LEFT JOIN wpmb_comments AS cmt ON ( cmt.comment_post_ID = wpmb_posts.ID ) LEFT JOIN wpmb_postmeta AS m ON (wpmb_posts.ID = m.post_id) WHERE 1=1 AND ( ( (((wpmb_posts.post_title LIKE ‘%second%’) OR (wpmb_posts.post_content LIKE ‘%second%’)) AND ((wpmb_posts.post_title LIKE ‘%homes%’) OR (wpmb_posts.post_content LIKE ‘%homes%’) OR (((tter.name LIKE ‘%second%’) AND (tter.name LIKE ‘%homes%’)) OR (tter.name LIKE ‘%second homes%’)) OR (((tter.slug LIKE ‘%second%’) AND (tter.slug LIKE ‘%homes%’)) OR (tter.slug LIKE ‘%second-homes%’)) OR (((ttax.description LIKE ‘%second%’) AND (ttax.description LIKE ‘%homes%’)) OR (ttax.description LIKE ‘%second homes%’)) OR (((m.meta_value LIKE ‘%second%’) AND (m.meta_value LIKE ‘%homes%’)) OR (m.meta_value LIKE ‘%second homes%’)) OR (((wpmb_posts.post_excerpt LIKE ‘%second%’) AND (wpmb_posts.post_excerpt LIKE ‘%homes%’)) OR (wpmb_posts.post_excerpt LIKE ‘%second homes%’)) OR (((((cmt.comment_content LIKE ‘%second%’) AND (cmt.comment_content LIKE ‘%homes%’)) OR (cmt.comment_content LIKE ‘%second homes%’)) OR (((cmt.comment_author LIKE ‘%second%’) AND (cmt.comment_author LIKE ‘%homes%’)) OR (cmt.comment_author LIKE ‘%second homes%’))) AND cmt.comment_approved = ‘1’) )) AND (wpmb_posts.post_password = ‘’) AND wpmb_posts.post_type = ‘post’ AND (wpmb_posts.post_status = ‘publish’ OR wpmb_posts.post_status = ‘attachment’ OR wpmb_posts.post_status = ‘draft’)) AND post_type != ‘revision’) AND post_status != ‘future’ ORDER BY wpmb_posts.post_date DESC LIMIT 0, 10
Explain output is:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wpmb_posts
type: range
possible_keys: type_status_date
key: type_status_date
key_len: 124
ref: NULL
rows: 8408
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: trel
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mbblogs.wpmb_posts.ID
rows: 574
Extra: Using index; Distinct
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ttax
type: eq_ref
possible_keys: PRIMARY,taxonomy
key: PRIMARY
key_len: 8
ref: mbblogs.trel.term_taxonomy_id
rows: 1
Extra: Distinct
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: tter
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mbblogs.ttax.term_id
rows: 1
Extra: Distinct
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: cmt
type: ref
possible_keys: comment_post_ID
key: comment_post_ID
key_len: 8
ref: mbblogs.wpmb_posts.ID
rows: 19
Extra: Distinct
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: post_id
key: post_id
key_len: 8
ref: mbblogs.wpmb_posts.ID
rows: 4
Extra: Using where; Distinct
6 rows in set (0.01 sec)
Table status output:
mysql> show table status like ‘wpmb_posts’\G
*************************** 1. row ***************************
Name: wpmb_posts
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 43757
Avg_row_length: 3490
Data_length: 152755284
Max_data_length: 281474976710655
Index_length: 82311168
Data_free: 0
Auto_increment: 48497
Create_time: 2013-01-03 15:38:38
Update_time: 2013-01-21 15:11:56
Check_time: 2013-01-03 15:39:12
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
From above status size of table is approx 224MB
Number of rows in table are : 43757
As per the explain out put this query is using many joins and distinct function which require tmp files to be used in memory and disk.
I increased tmp_table_size and max_heap_table_size to 1 GB, but no gain.
The profile output is:
mysql> SHOW PROFILE CPU FOR QUERY 5;
±-------------------------------±----------±----------±- ----------+
| Status | Duration | CPU_user | CPU_system |
±-------------------------------±----------±----------±- ----------+
| starting | 0.000014 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000006 | 0.000000 | 0.000000 |
| checking query cache for query | 0.000158 | 0.000000 | 0.000000 |
| checking permissions | 0.000008 | 0.000000 | 0.000000 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 |
| Opening tables | 0.000092 | 0.000000 | 0.000000 |
| System lock | 0.000018 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000047 | 0.000000 | 0.000000 |
| init | 0.000079 | 0.000000 | 0.000000 |
| optimizing | 0.000037 | 0.000000 | 0.000000 |
| statistics | 0.000403 | 0.001000 | 0.000000 |
| preparing | 0.000043 | 0.000000 | 0.000000 |
| Creating tmp table | 0.000245 | 0.000000 | 0.000000 |
| executing | 0.000008 | 0.000000 | 0.000000 |
| Copying to tmp table | 26.546441 | 36.603435 | 2.347643 |
| Sorting result | 0.000816 | 0.001000 | 0.001000 |
| Sending data | 0.000131 | 0.000000 | 0.000000 |
| end | 0.000011 | 0.000000 | 0.000000 |
| removing tmp table | 0.000478 | 0.000000 | 0.000000 |
| end | 0.000010 | 0.000000 | 0.000000 |
| query end | 0.000011 | 0.000000 | 0.000000 |
| closing tables | 0.000023 | 0.000000 | 0.000000 |
| freeing items | 0.000040 | 0.000000 | 0.000000 |
| logging slow query | 0.000008 | 0.000000 | 0.000000 |
| logging slow query | 0.000043 | 0.000000 | 0.000000 |
| cleaning up | 0.000010 | 0.000000 | 0.000000 |
±-------------------------------±----------±----------±- ----------+
my question is why this query taking so much time?
when table size is not high
when average row length not high
when tmp_table_size is high
when number of rows is not high