MySQL Forums
Forum List  »  Optimizer & Parser

Re: index not used when join condition added
Posted by: Daniel Walter
Date: January 03, 2012 09:49PM

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'


Options: ReplyQuote


Subject
Views
Written By
Posted
Re: index not used when join condition added
1037
January 03, 2012 09:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.