Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

I wanted to create bitmap index on a column say region as it has less 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

share|improve this question

2 Answers 2

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.

share|improve this answer
2  
As an addition, both MyISAM and InnoDB (since 5.6) support FULLTEXT indexes and RTREE/Spatial indexes (InnoDB since 5.7). –  jynus Jul 17 '14 at 18:48
1  
Thanks - good for completeness. –  Vérace Jul 17 '14 at 18:55

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.

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.

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,

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.