FW: cache-panel DB issue

------_=_NextPart_001_01CA363A.945E3BD4
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.

=20

Cache database (ndb)

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

=20

CREATE TABLE `panel`.`cache1` (

`panelId` int(11) DEFAULT NULL,

KEY `panelid` (`panelId`)

) ENGINE=3Dndbcluster DEFAULT CHARSET=3Dutf8;

=20

CREATE TABLE `panel`.`cache2` (

`panelId` int(11) DEFAULT NULL,

KEY `panelid` (`panelId`)

) ENGINE=3Dndbcluster DEFAULT CHARSET=3Dutf8;

=20

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

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

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

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

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

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

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

=20

Panel database (myisam)

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

=20

CREATE TABLE `panel`.`cache3` (

`panelId` int(11) DEFAULT NULL,

KEY `panelid` (`panelId`)

) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8;

=20

CREATE TABLE `panel`.`cache4` (

`panelId` int(11) DEFAULT NULL,

KEY `panelid` (`panelId`)

) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8;

=20

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

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

| 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 |=20

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

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

=20

Thanks Joshua Gordon


------_=_NextPart_001_01CA363A.945E3BD4--