Hi Rick,
All the requested info is below. The only query that is taking a long time under under load is the one I originally posted.
Thanks again for your help,
Ben
mysql> SELECT VERSION()\G
*************************** 1. row ***************************
VERSION(): 5.0.51a-24+lenny2-log
mysql> show table status LIKE "documents"\G
*************************** 1. row ***************************
Name: documents
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 83837104
Avg_row_length: 239
Data_length: 20068368384
Max_data_length: 0
Index_length: 22740664320
Data_free: 0
Auto_increment: 127398909
Create_time: 2009-09-08 18:00:02
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 8192 kB
1 row in set (0.16 sec)
mysql> show table status LIKE "profile_documents"\G
*************************** 1. row ***************************
Name: profile_documents
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4181505
Avg_row_length: 63
Data_length: 266141696
Max_data_length: 0
Index_length: 420773888
Data_free: 0
Auto_increment: 5310987
Create_time: 2009-09-10 15:58:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 8192 kB; (`document_id`) REFER `Mendeley/documents`(`id`); (`profil
1 row in set (1.36 sec)
mysql> show table status like "document_references"\G
*************************** 1. row ***************************
Name: document_references
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 57194186
Avg_row_length: 59
Data_length: 3430809600
Max_data_length: 0
Index_length: 2709356544
Data_free: 0
Auto_increment: NULL
Create_time: 2009-09-08 18:00:02
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 9216 kB; (`document_id`) REFER `Mendeley/documents`(`id`) ON DELETE
1 row in set (4.91 sec)
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+-------------+
| Variable_name | Value |
+-------------------------------+-------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 21474836480 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 1073737728 |
| key_buffer_size | 67108864 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+-------------+
12 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE documents\G
*************************** 1. row ***************************
Table: documents
Create Table: CREATE TABLE `documents` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`unique_id` varchar(255) default NULL,
`canonical_id` bigint(20) unsigned default NULL,
`type` enum('journal','book','generic','book_section','conference_proceedings','working_paper') default NULL,
`doi` varchar(255) default NULL,
`isbn` varchar(255) default NULL,
`title` varchar(255) NOT NULL,
`confirmed` tinyint(1) default NULL,
`added` datetime default NULL,
`published_in` varchar(255) default NULL,
`year` int(11) default NULL,
`month` int(11) default NULL,
`day` int(11) default NULL,
`time` time default NULL,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_unique_id` (`unique_id`),
KEY `idx_title` (`title`),
KEY `idx_doi` (`doi`)
) ENGINE=InnoDB AUTO_INCREMENT=127400786 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
mysql> SHOW CREATE TABLE profile_documents\G
*************************** 1. row ***************************
Table: profile_documents
Create Table: CREATE TABLE `profile_documents` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`profile_id` bigint(20) unsigned default NULL,
`document_id` bigint(20) unsigned default NULL,
`added` datetime default NULL,
`author` tinyint(1) default '0',
`alt_id` int(7) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_profile_document` (`profile_id`,`document_id`),
KEY `profile_documents_fk_Document_id` (`document_id`),
KEY `idx_alt_id` (`alt_id`),
CONSTRAINT `profile_documents_fk_Document_id` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`),
CONSTRAINT `profile_documents_fk_Profile_id` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5311259 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE document_references\G
*************************** 1. row ***************************
Table: document_references
Create Table: CREATE TABLE `document_references` (
`document_id` bigint(20) unsigned NOT NULL,
`referenced_document_id` bigint(20) unsigned NOT NULL,
`position` int(11) NOT NULL,
PRIMARY KEY (`document_id`,`referenced_document_id`,`position`),
KEY `document_references_fk_Referenced_document_id` (`referenced_document_id`),
CONSTRAINT `document_references_fk_Document_id` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
CONSTRAINT `document_references_fk_Referenced_document_id` FOREIGN KEY (`referenced_document_id`) REFERENCES `documents` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM document_references WHERE referenced_document_id = '118084217' OR document_id = '118084217'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: document_references
type: index_merge
possible_keys: PRIMARY,document_references_fk_Referenced_document_id
key: document_references_fk_Referenced_document_id,PRIMARY
key_len: 8,8
ref: NULL
rows: 2
Extra: Using union(document_references_fk_Referenced_document_id,PRIMARY); Using where