On Thu, 2009-10-01 at 11:31 -0700, Tarandeep Singh wrote:
> Hi,
>
> I am trying to use IN clause in my query. And mysql cluster is not using
> primary key. Is there any workaround for this ?
> Here are my tables-
>
> Table 1: Primary key = domain_name (created using Hash)
>
> If I run this query-
> mysql> explain select * from table1 where domain_name in ( 'abc.com', '
> xyz.com');
>
> Then the primary key is NOT used as shown in the explain plan:
>
> select_type:SIMPLE
> table: table1
> type: ALL
> possible keys: PRIMARY
> key: NULL
>
> Similarly, If I try to use a nested query, in the IN clause same problem and
> this will make query run slow. Any thoughts?
How many rows are in the `table1`? What version of NDB are you using? I
am not able to reproduce the described behavior in ndb-7.0.7
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.35-ndb-7.0.7-log |
+----------------------+
1 row in set (0.02 sec)
mysql> CREATE TABLE `t2` (
`domain_name` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`domain_name`) USING HASH
) ENGINE=ndbcluster;
INSERT INTO `t2` (`domain_name`) VALUES
('asdfadfasdf'),('aavwefwc'),('ajinincaincoiewnc'),('nionfaonencwna'),('rrthsvervare'),('gaawevcaweverahgag'),('aagwergavvwaew'),('wvageahrgrav'),('agagwegawegewg'),('agehhseha'),('erhahrhaehaerh'),('aeahreahrrewhsehr'),('hahrhahaerhaerhre'),('zzreyargzyzry'),('haerhahzrzRyeyw'),('aharzyarhhaeraer'),('ahraarthwe'),('aherbqarbaerhtrfjrt'),('sjtrdnstrjjstrj'),('sjmtrjdmjsjmrtjaja'),('sjksartjaaerh'),('ahahjaerhrzryzdryh'),('sashjsbtajrbzx'),('zrehsbtjtahf'),('shethrtsjgf4j'),('shbry3h'),('fbsthjhsnrzyvr5'),('zsrfbgnjyfghrt'),('snrtbxdcfnhtr4'),('nsrbxgnynjdhzvrz'),('zeyjdtbsfbbrs'),('zefgbjjxgzgrae'),('argavhbgtbnsryzdf'),('gzrgebgjhdea'),('gstnydtxfgcbstbbr'),('bsdhrebjydhjb'),('rbhstbtjdfnmtyetmm'),('dtmtyndyygkghjsrb'),('xjsrbtnyxdghjmdz'),('
shetnysdjjwrth6nbxd'),('sdbhtbsdjgn5hjejsr'),('thshsthjktukuyg'),('shstb4nsjfgb6hz'),('strm,dhgnybjksf'),('sjydgjdghj'),('sbstrndjsdghjwrmsn'),('sbnsgbsrmksnmsnsr'),('tjnsntrnsgjcf'),('ksjsrgnbstrnjfgtntnsjtr'),('jsnttaherhfgsnyr'),('bzdzbtrhzhbabnzsg'),('natznrnynrrzj');
mysql> EXPLAIN SELECT * FROM `t2` WHERE `domain_name` =
'haerhahzrzRyeyw'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM `t2` WHERE `domain_name` IN
('haerhahzrzRyeyw','tjnsntrnsgjcf','zsrfbgnjyfghrt')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: NULL
rows: 3
Extra: Using where with pushed condition
1 row in set (0.00 sec)
--
Matthew Montgomery
MySQL Senior Support Engineer
Sun Microsystems Inc., Database Group
San Antonio, Texas, USA
