CREATE TABLE `eodxmlperiods` (
`datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`current` float DEFAULT NULL,
`volume` float DEFAULT NULL,
`symbolid` int(11) DEFAULT NULL,
KEY `eodxmlperiods1` (`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `idfromsymbol` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`symbol` varchar(40) DEFAULT NULL,
`exchange` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idfromsymbol1` (`exchange`,`symbol`)
) ENGINE=InnoDB AUTO_INCREMENT=599947 DEFAULT CHARSET=utf8
Show table status like 'idfromsymbol'
'idfromsymbol', 'InnoDB', '10', 'Compact', '623921', '49', '30998528', '0', '24723456', '2710568960', '599947', '2012-01-01 13:58:49', NULL, NULL, 'utf8_general_ci', NULL, '', ''
Show table status like 'eodxmlperiods'
'eodxmlperiods', 'InnoDB', '10', 'Compact', '11804607', '44', '530579456', '0', '249413632', '2710568960', NULL, '2011-12-11 23:15:36', NULL, NULL, 'utf8_general_ci', NULL, '', ''
explain select count(*) from eodxmlperiods, idfromsymbol where eodxmlperiods.datetime BETWEEN '2011-12-12 01:00:00' AND '2011-12-12 23:00:00' AND eodxmlperiods.symbolid=idfromsymbol.id AND idfromsymbol.exchange='AMEX';
'1', 'SIMPLE', 'eodxmlperiods', 'ALL', 'eodxmlperiods1', NULL, NULL, NULL, '11804607', 'Using where'
'1', 'SIMPLE', 'idfromsymbol', 'eq_ref', 'PRIMARY,idfromsymbol1', 'PRIMARY', '4', 'stock.eodxmlperiods.symbolid', '1', 'Using where'
explain select count(*) from eodxmlperiods where eodxmlperiods.datetime BETWEEN '2011-12-12 01:00:00' AND '2011-12-12 23:00:00'
'1', 'SIMPLE', 'eodxmlperiods', 'range', 'eodxmlperiods1', 'eodxmlperiods1', '4', NULL, '2235278', 'Using where; Using index'
Show variables like '%buffer%'
'bulk_insert_buffer_size', '8388608'
'innodb_buffer_pool_instances', '1'
'innodb_buffer_pool_size', '31457280'
'innodb_change_buffering', 'all'
'innodb_log_buffer_size', '1048576'
'join_buffer_size', '131072'
'key_buffer_size', '8388608'
'myisam_sort_buffer_size', '16777216'
'net_buffer_length', '16384'
'preload_buffer_size', '32768'
'read_buffer_size', '28672'
'read_rnd_buffer_size', '262144'
'sort_buffer_size', '262144'
'sql_buffer_result', 'OFF'