NDB Ordered Index Status

Alpha release Jan 30, 2004

• Up to 32 index attributes of any type, possibly nullable.
• Index build i.e. table need not be empty.
• Logging NOT done, index rebuilt at system restart.
• Single range scan with lower and upper bounds.
• Scan with locking: read latest, read for update.
• LIMITED number of parallel scans.
• Total result set NOT in index key order.
• NDB ODBC optimizer to use ordered index for equality but NOT for ranges.
• MySQL optimizer to use ordered index for equality and ranges.

As an example, consider following index on integer attributes.

SQL>create index X on T (A, B, C) nologging;

Single range scan means that bounds are set on an initial sequence of index keys, and all but last is an equality.
For example following scans are supported (the last 2 not via NDB ODBC).

SQL>select * from T where A = 1;
SQL>select * from T where A = 1 and B = 10 and C = 20;
SQL>select * from T where A < 10;
SQL>select * from T where A = 1 and 10 < B and B < 20;

Following scans are NOT supported:

SQL>select * from T where B = 1;
SQL>select * from T where A < 10 and B < 20;

Features and dates

Feature	Now	Jan 30	Mar 01	Never
Index maintenance	X	-	-	-
Basic scan	X 1)	-	-	-
Scan bounds on nullable attributes	-	X	-	-
Scan with locking	-	X	-	-
NDB ODBC equality bounds	-	X	-	-
MySQL integration	-	X	-	-
Index build	2)	X	-	-
Unlimited number of scans	3)	-	X	-
Total ordering	-	-	X	-
Multiple range scan	-	-	X	-
NDB ODBC range bounds	-	-	-	X
Logging	-	-	-	X

1) No locking and bounds must be on non-nullable key attributes.
2) Currently table must be empty when index is created.
3) Currently limited to 11 simultaneous per fragment.