FW: cache-panel DB issue

[Joshua Gordon]

I am trying to move my cache into ndb but when I use the ndb engine the
query time is 4 min versus myisam which is 7 seconds. This is due to the
fact it is doing a full table scan on ndb and an index on myisam. I was
hoping someone could help me fix this or at least explain why this is
happening. I have included the select statements , the select statement
and the explain select for both the ndb and the myisam.

 

Cache database (ndb)

  Select distinct t0.panelId from cache1 t0, cache2  t1 where
t0.panelid=t1.panelid; (282.54 s)

 

CREATE TABLE  `panel`.`cache1` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

 

CREATE TABLE  `panel`.`cache2` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

 

Explain select distinct t0.panelId from cache1 t0, cache2 t1 where
t0.panelid=t1.panelid; 

+----+-------------+-------+------+---------------+---------+---------+-
-----------------+--------+-------------+

| id | select_type | table | type | possible_keys | key     | key_len |
ref              | rows   | Extra       |

+----+-------------+-------+------+---------------+---------+---------+-
-----------------+--------+-------------+

|  1 | SIMPLE      | t0    | ALL  | panelid       | NULL    | NULL    |
NULL             | 534063 |             | 

|  1 | SIMPLE      | t1    | ref  | panelid       | panelid | 5       |
cache.t0.panelId |      1 | Using where | 

+----+-------------+-------+------+---------------+---------+---------+-
-----------------+--------+-------------+

 

Panel database (myisam)

select distinct t0.panelId from cache3 t0, cache4 t1 where
t0.panelid=t1.panelid;  (6.5 s)

 

CREATE TABLE  `panel`.`cache3` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

CREATE TABLE  `panel`.`cache4` (

  `panelId` int(11) DEFAULT NULL,

  KEY `panelid` (`panelId`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

explain select distinct t0.panelId from cache3 t0, cache4 t1 where
t0.panelid=t1.panelid; 

+----+-------------+-------+-------+---------------+---------+---------+
------------------+--------+------------------------------------+

| id | select_type | table | type  | possible_keys | key     | key_len |
ref              | rows   | Extra                              |

+----+-------------+-------+-------+---------------+---------+---------+
------------------+--------+------------------------------------+

|  1 | SIMPLE      | t0    | index | panelid       | panelid | 5       |
NULL             | 534053 | Using index; Using temporary       | 

|  1 | SIMPLE      | t1    | ref   | panelid       | panelid | 5       |
panel.t0.panelId |      1 | Using where; Using index; Distinct | 

+----+-------------+-------+-------+---------------+---------+---------+
------------------+--------+------------------------------------+

 

Thanks Joshua Gordon