Thanks for the response.
Answers to your notes:
* If id is UNIQUE, then no DISTINCT is needed. but it is not declared UNIQUE; why?
> There are multiple versions of each row in the table, and the set of versions
> for an object all share the same id. Id combined with TORM_REVISION_ID is
> unique though. Also doing a select where TORM_IS_CURRENT_REVISION=1 will
> return one instance of each id.
* There are a lot of redundant indexes. When one non-UNIQUE INDEX is a prefix of another, DROP the shorter one.
> Most are just test indexes where I'm trying to find an index that works for a
> particular query, so most aren't on the production server. Thanks for the tip
> though, that's useful to know.
* TORM_IS_CURRENT_REVISION seems to be NULLable -- perhaps a mistake?
> Yes, fixed now thanks. Actually it looks like there are loads of nullables
> that shouldn't be...
* I hope press_release has an index starting with (id).
> It does. I've given a new example query that better summarises my issues though
> as the one in my second post above joins two tables that have a 1:1
> relationship, but the reason I'm including the DISTINCT is that I'm joining
> onto an m:n link table (section_to_article). Example below.
SELECT DISTINCT article.* FROM article
LEFT OUTER JOIN section_to_article ON ( article.id=section_to_article.article_id ) AND (section_to_article.section_id = 2)
WHERE
(article.published = 1)
AND ( article.TORM_IS_CURRENT_REVISION = 1 )
ORDER BY article.article_date DESC,article.id DESC
LIMIT 50;
mk-visual-explain:
Filesort
+- TEMPORARY
table temporary(article,section_to_article)
+- JOIN
+- Distinct/Not-Exists
| +- Bookmark lookup
| +- Table
| | table section_to_article
| | possible_keys idx_section_to_art_art_id,idx_section_to_art_sec_id
| +- Index lookup
| key section_to_article->idx_section_to_art_art_id
| possible_keys idx_section_to_art_art_id,idx_section_to_art_sec_id
| key_len 5
| ref my_db.article.id
| rows 1
+- Filter with WHERE
+- Bookmark lookup
+- Table
| table article
| possible_keys article_published,art_is_current_rev,art_comp_x3,art_comp_x4,acx10,acx12,acx15
+- Index lookup
key article->acx10
possible_keys article_published,art_is_current_rev,art_comp_x3,art_comp_x4,acx10,acx12,acx15
key_len 3
ref const,const
rows 7714
Table defs:
mysql> SHOW CREATE TABLE article\G
*************************** 1. row ***************************
Table: article
Create Table: CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` enum('feature','interview','news') DEFAULT 'news',
`headline` varchar(255) DEFAULT NULL,
`article_date` int(11) DEFAULT NULL,
`summary` varchar(255) DEFAULT NULL,
`body` text,
`thumbnail_asset_id` int(11) DEFAULT NULL,
`user_submitted` tinyint(4) DEFAULT NULL,
`article_end_date` int(11) DEFAULT NULL,
`published` tinyint(1) DEFAULT NULL,
`dirty` enum('dirty','staging_clean','live_clean') DEFAULT 'dirty',
`author_id` int(11) DEFAULT NULL,
`creation_date` int(11) DEFAULT NULL,
`modification_date` int(11) DEFAULT NULL,
`last_modified_by_user_id` int(11) DEFAULT NULL,
`home_section_id` int(11) DEFAULT NULL,
`root_template_id` int(11) DEFAULT NULL,
`keywords` text,
`byline` varchar(255) DEFAULT NULL,
`TORM_CLASS` varchar(255) DEFAULT NULL,
`friendly_path_title` varchar(255) DEFAULT NULL,
`TORM_REVISION_ID` int(11) DEFAULT NULL,
`TORM_IS_CURRENT_REVISION` tinyint(1) NOT NULL,
`TORM_REVISION_LABEL` varchar(255) DEFAULT NULL,
`TORM_REVISION_DATE` int(11) DEFAULT NULL,
`TORM_REVISION_USER_ID` int(11) DEFAULT NULL,
`locale` varchar(255) DEFAULT 'Default',
KEY `torm_class_article` (`TORM_CLASS`),
KEY `article_published` (`published`),
KEY `article_date` (`article_date`),
KEY `idx_article_dirty` (`dirty`),
KEY `article_composite` (`id`,`TORM_REVISION_ID`),
KEY `article_composite2` (`id`,`TORM_REVISION_ID`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`published`),
KEY `art_is_current_rev` (`TORM_IS_CURRENT_REVISION`),
KEY `art_comp_x2` (`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
KEY `art_comp_x3` (`published`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
KEY `art_comp_x4` (`published`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
KEY `art_comp_x5` (`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
KEY `art_id` (`id`),
KEY `art_comp_x6` (`id`,`published`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`),
KEY `art_comp_x7` (`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`id`),
KEY `acx9` (`article_date`,`id`,`published`,`TORM_IS_CURRENT_REVISION`),
KEY `acx10` (`published`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
KEY `acx11` (`TORM_REVISION_ID`,`published`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
KEY `acx12` (`published`,`TORM_IS_CURRENT_REVISION`,`TORM_REVISION_ID`,`article_date`,`id`),
KEY `acx13` (`id`,`published`,`TORM_IS_CURRENT_REVISION`,`TORM_REVISION_ID`,`article_date`),
KEY `acx14` (`article_date`,`published`,`TORM_IS_CURRENT_REVISION`,`id`),
KEY `acx15` (`published`,`TORM_IS_CURRENT_REVISION`,`article_date`),
KEY `acx16` (`article_date`,`published`,`TORM_IS_CURRENT_REVISION`,`id`),
FULLTEXT KEY `art_search_head` (`headline`),
FULLTEXT KEY `art_search_summ` (`summary`),
FULLTEXT KEY `art_search_body` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=537599 DEFAULT CHARSET=utf8
mysql> SHOW CREATE TABLE section_to_article\G
*************************** 1. row ***************************
Table: section_to_article
Create Table: CREATE TABLE `section_to_article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`section_id` int(11) DEFAULT NULL,
`article_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_section_to_art_art_id` (`article_id`),
KEY `idx_section_to_art_sec_id` (`section_id`,`article_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10059 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
(idx_section_to_art_sec_id is badly named...)
Buffers / table info:
mysql> SHOW TABLE STATUS LIKE 'article'\G
*************************** 1. row ***************************
Name: article
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 22356
Avg_row_length: 2902
Data_length: 64879816
Max_data_length: 281474976710655
Index_length: 49170432
Data_free: 0
Auto_increment: 537599
Create_time: 2010-02-25 18:19:53
Update_time: 2010-02-25 18:19:55
Check_time: 2010-02-25 18:20:29
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'section_to_article'\G
*************************** 1. row ***************************
Name: section_to_article
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10812
Avg_row_length: 146
Data_length: 1589248
Max_data_length: 0
Index_length: 475136
Data_free: 27262976
Auto_increment: 10059
Create_time: 2010-02-23 15:12:16
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.04 sec)
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 67108864 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 268435456 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 8388608 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
I've just noticed that section_to_article is InnoDB actually, I'd forgotten about that. It was done that way because when it was MyISAM it kept getting corrupted. Article remains MyISAM because it's using fulltext indexing. I plan to move everything to InnoDB soon but first have to develop an alternate search indexing system to integrate with Solr.
Thanks for your help :)
Edited 1 time(s). Last edit at 02/25/2010 11:47AM by Tolan Blundell.