Re: Large table designissue
hey Rick,
Thanks for input, below are required details:
[ code ]
Create Table: CREATE TABLE `transactions` (
`id` bigint(20) NOT NULL auto_increment,
`maingroup_id` bigint(20) NOT NULL,
`group_id` bigint(20) default NULL,
`userid` bigint(20) NOT NULL default '0',
`guid` varchar(254) NOT NULL,
`pid` bigint(20) NOT NULL,
`subpid` varchar(254) NOT NULL,
`ipaddress` varchar(254) NOT NULL,
`transaction_time` datetime NOT NULL default '0000-00-00 00:00:00',
`status` enum('First','Second','Third','Fourth') NOT NULL,
`activitytime` datetime NOT NULL default '0000-00-00 00:00:00',
`review` enum('YES','NO','NA') NOT NULL default 'NO',
`revieweddate` datetime NOT NULL default '0000-00-00 00:00:00',
`amt` double(10,2) NOT NULL default '0.00',
`pcurrent_val` decimal(10,2) NOT NULL,
`history_id` bigint(20) default '0',
`history_type` enum('new','old') default NULL,
`data` varchar(254) NOT NULL,
`verified` enum('YES','NO') NOT NULL default 'NO',
`verifiedtime` datetime NOT NULL default '0000-00-00 00:00:00',
`cfile` varchar(254) NOT NULL default ' ',
`sessionid` varchar(254) NOT NULL,
`flag` enum('Y','N') NOT NULL default 'N',
`email` enum('Y','N') NOT NULL default 'N',
`site` varchar(254) NOT NULL,
`complete` enum('Y','N') NOT NULL default 'N',
`activity_status` enum('Active','Inactive','Dormant','NA') NOT NULL default 'NA',
`oactivitydatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`rssid` bigint(20) NOT NULL,
`flag_changed` enum('Y','N') NOT NULL default 'N',
`testyn` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `id_idx` (`id`),
KEY `fk_transactions_users` (`userid`),
KEY `group_id` (`group_id`),
KEY `sessionid` (`sessionid`),
KEY `guid` (`guid`),
KEY `status` (`status`),
KEY `pid` (`pid`),
KEY `rssid` (`rssid`),
KEY `history_id` (`history_id`),
KEY `activity_status` (`activity_status`),
KEY `history_type` (`history_type`),
KEY `maingroup_id` (`maingroup_id`),
KEY `flag_changed` (`flag_changed`)
) ENGINE=InnoDB AUTO_INCREMENT=16956487 DEFAULT CHARSET=latin1
mysql> SHOW TABLE STATUS LIKE 'transactions%' \G;
*************************** 1. row ***************************
Name: transactions
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 17054214
Avg_row_length: 365
Data_length: 6234832896
Max_data_length: 0
Index_length: 7219789824
Data_free: 0
Auto_increment: 16956487
Create_time: 2009-05-18 02:35:05
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 7168 kB
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| bdb_log_buffer_size | 262144 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 4718592000 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 201326592 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1044480 |
| read_rnd_buffer_size | 4190208 |
| sort_buffer_size | 4194296 |
+-------------------------------+------------+
explain select distinct subpid from transactions;
+----+-------------+--------------------+------+---------------+------+---------+------+----------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+------+---------+------+----------+-----------------+
| 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 17054214 | Using temporary |
+----+-------------+--------------------+------+---------------+------+---------+------+----------+-----------------+
[ /code ]