Re: Indexes in NDB tables not used

Hi Pavel,

There's no WHERE clause in your statement, so Innodb used a index scan to get data in one index tree, But in NDB, I think, PKs are hashed to different data nodes, it would scan all data in all nodes rather than in one index tree.

Try to use
explain select * from pokusndb where i=1; It will definitely use index.
Pavel Janík 写道:
> Hi,
>
> my NDB tables do not use indexes as InnoDB tables.
>
> To try yourself:
>
> DROP DATABASE pjtest;
> CREATE DATABASE pjtest;
> USE pjtest;
> CREATE TABLE pokusndb (i INT, PRIMARY KEY (i) ) ENGINE=NDBCLUSTER;
> INSERT INTO pokusndb () VALUES (4);
> INSERT INTO pokusndb () VALUES (1);
> CREATE TABLE pokus (i INT, PRIMARY KEY (i) );
> INSERT INTO pokus () VALUES (4);
> INSERT INTO pokus () VALUES (1);
>
> explain select * from pokus;
> explain select * from pokusndb;
>
> The result is:
>
> mysql> explain select * from pokus;
> +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
>
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
>
> | 1 | SIMPLE | pokus | index | NULL | PRIMARY | 4 | NULL | 2 | Using
> index |
> +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
>
> 1 row in set (0.00 sec)
>
> mysql> explain select * from pokusndb;
> +----+-------------+----------+------+---------------+------+---------+------+------+-------+
>
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+----------+------+---------------+------+---------+------+------+-------+
>
> | 1 | SIMPLE | pokusndb | ALL | NULL | NULL | NULL | NULL | 2 | |
> +----+-------------+----------+------+---------------+------+---------+------+------+-------+
>
> 1 row in set (0.00 sec)
>
> -> NDB table doesn't use index. Why? Do you have any idea what to try?
>
> My config:
>
> [ndbd default]
> NoOfReplicas=2
> MaxNoOfConcurrentOperations=128000
> MaxNoOfConcurrentTransactions=65536
> MaxNoOfLocalOperations=1500000
> DataMemory=1024M
> IndexMemory=512M
> TimeBetweenWatchDogCheck=30000
> DataDir=/var/lib/mysql-cluster
> MaxNoOfOrderedIndexes=512
> RedoBuffer=128M
>

-- 
Carrie Chen, Support Engineer, Asia-Pacific
MySQL AB, Beijing, China
http://www.mysql.com 


-- 
MySQL Cluster Mailing List
For list archives: 
http://lists.mysql.com/cluster
To unsubscribe:    
http://lists.mysql.com/cluster?unsub=lists@pantek.com