ON creating BITMAP INDEX on a table in mysql throws error

Question

I have database named "demo_dev" and added a table named "countries".

I wanted to create a bitmap index on a column, say, region as it has fewer unique values, using following query.

 CREATE BITMAP INDEX ON countries (region);

It threw an error as shown below:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BITMAP INDEX ON countries (region)'

What shoud be done for it to run successfully?

I just refered the following link : http://dev.mysql.com/worklog/task/?id=1524

mysql version : 5.5

Answer 1

You are looking at a feature request. See here for this table

Storage Engine Permissible Index Types
InnoDB BTREE
MyISAM BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH, BTREE (see note in text)

They are not even available in 5.7 - they would be a nice to have, but not yet - maybe in the future. Check this out - pages 32 and 33 - not even the columnar stores (InfiniDB and Inforbright) have them yet - AFAICS.

• jynus: As an addition, both MyISAM and InnoDB (since 5.6) support FULLTEXT indexes and RTREE/Spatial indexes (InnoDB since 5.7).

Answer 2

You can use fastbit bitmap index, from mysql, using fastbit UDF. Check this link https://github.com/greenlion/FastBit_UDF

About these UDF Functions and FastBit

FastBit is a data store which implements WAH (word aligned hybrid) bitmap indexes. These UDF create, modify and query FastBit tables. The UDF treats a single directory on the filesystem as one FastBit table. Inside of the FastBit table/directory are directories representing partitions. The partitions are created automatically when data is loaded.

All functions take as the first argument the table path/directory

FastBit WAH bitmap indexes are optimal for multi-dimensional range scans, unlike b-tree indexes which are optimal only for one-dimensional queries. This means that FastBit can very efficiently handle queries that MySQL can not, like select c1 from table where c2 between 1 and 20 or c3 between 1 and 90 or c4 in (1,2,3). MySQL can not answer that query using a b-tree index and will resort to a full table scan.

All columns of a fastbit table are automatically bitmapped indexed.

The UDFs functions provided, are: fb_helper, fb_inlist, fb_create, fb_load, fb_query, fb_debug, fb_unlink, fb_delete, fb_insert, fb_insert2, fb_resort

There are also several limitations when querying a table (using fb_query functionality), see the READ.ME link for details.