I have a MySQL query that is running very slowly because it's not using an index on the join key. What is wrong with my table structure that the optimizer is not using the index?
mysql> EXPLAIN SELECT * FROM indicator_performance_averages
LEFT OUTER JOIN `prof` ON (`prof`.`symbol` = indicator_performance_averages.`symbol`)
WHERE (indicator_performance_averages.`symbol` = 'ZCN13');
+----+-------------+--------------------------------+-------+---------------+------------+---------+-------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------------+-------+---------------+------------+---------+-------+---------+-------+
| 1 | SIMPLE | indicator_performance_averages | const | idx_symbol | idx_symbol | 98 | const | 1 | |
| 1 | SIMPLE | prof | ALL | NULL | NULL | NULL | NULL | 1102075 | |
+----+-------------+--------------------------------+-------+---------------+------------+---------+-------+---------+-------+
mysql> DESCRIBE indicator_performance_averages;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| symbol | varchar(32) | NO | UNI | NULL | |
| date | date | YES | | NULL | |
| last_update | timestamp | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
mysql> DESCRIBE prof;
+---------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------------------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| symbol | varchar(32) | NO | UNI | NULL | |
| name | varchar(128) | NO | | NULL | |
| lastupdate | datetime | NO | | 0000-00-00 00:00:00 | |
+---------------+---------------------+------+-----+---------------------+----------------+
EDIT: Adding SHOW CREATE TABLE for both tables:
CREATE TABLE `indicator_performance_averages` (
`id` int(11) unsigned NOT NULL auto_increment,
`symbol` varchar(32) character set utf8 NOT NULL,
`date` date default NULL,
`last_update` timestamp NULL default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_symbol` (`symbol`)
) ENGINE=InnoDB AUTO_INCREMENT=6719 DEFAULT CHARSET=latin1
CREATE TABLE `prof` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`symbol` varchar(32) NOT NULL,
`name` varchar(128) NOT NULL,
`lastupdate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
UNIQUE KEY `IDX_symbol` (`symbol`),
) ENGINE=InnoDB AUTO_INCREMENT=37736071 DEFAULT CHARSET=latin1
indicator_performance_averages.idx_symbol
index on the JOIN. It will only use one index on a JOIN because it already knows the value on the other side. – Mike W Jul 11 at 21:21symbol
columns encoded? – eggyal Jul 11 at 21:21prof.symbol
to show up in the possible_keys column in the plan. There's not enough information here to debug the problem. I think we'd need the output fromSHOW CREATE TABLE
for both of the tables, in order to identify the problem. Since this is a VARCHAR column, the likely suspects are a difference in characterset or collation. – spencer7593 Jul 11 at 21:24