0

I am trying to join two tables which are on two different engines. One is on Innodb and other is on Tokudb.

Query is something like:

select * from table1 t1, table2 t2 where t1.a = t2.x and t1.b = 'xyz';

Here table1 has a(indexed), b(indexed), c columns and has engine innodb
while table2 has x(indexed), y, z columns and has engine tokudb

It is scanning through all the rows of table2 and using join type: ALL

Result of explaining the query

id  select_type table partitions type   possible_keys   key   key_len ref     rows  filtered    Extra
1   SIMPLE      t1    NULL       const  b,a             b     303     const   1     100.00      NULL
1   SIMPLE      t2    NULL       ALL    NULL            NULL  NULL    NULL    20687 100.00      Using where

As you can see it is scanning over the entire table2. Am i missing something here? or is it because it is joining over two different engines?

Create Table Syntax

table1

CREATE TABLE `table1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`),
  UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

table2

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` varchar(100) DEFAULT NULL,
  `y` varchar(100) DEFAULT NULL,
  `z` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `x` (`x`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1;
MrTambourineMan
  • 1,025
  • 1
  • 11
  • 19

1 Answers1

1

Use the same CHARACTER SET when joining:

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
) ENGINE=TokuDB DEFAULT CHARSET=latin1;
                                ^^^^^^
Rick James
  • 135,179
  • 13
  • 127
  • 222