My opinion is that partitioning of that table in the manner presented may be useful.

Your queries will be two times faster (in the case when partitions are almost equal) if they make full scans.

In case your queryes have another tone of filters/conditions and use indexes, partitioning is not useful, because the level of an index is almost not affected by doubling the number of values.

UPDATE: For the test you did (SELECT * FROM table1 WHERE TENANTID=2) it is sure that partitioning is best. The bitmap solution need to scan the index, and after this, to scan all table's blocks containing rows with tenantid=2(it knows what are the rows). But partitioning will cause just scanning the table's partition with tenantid=2. They are phisically separated to tenantid=1.

So, two scans(index scan + table scan) vs one table scan(wich may be smaller).

Let's compare them

PARTITION SIZE

If you have the following:

• 100 million rows in a table
• BTREE indexing
• Each Page in the BTREE holds 1024 keys

What would the metrics look like?

Since LOG(100000000)/LOG(2) = 26.575424759099, a BTREE index with 1024 keys per page treenode would have a tree height of only 3 (CEILING(LOG(100000000)/LOG(1024))). With only three pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 30 keys.

NUMBER OF PARTITIONS

If you have the following:

• 100 million rows in a table
• BTREE indexing
• Each Page in the BTREE holds 1024 keys
• You create 1024 parititions

The numbers would be slightly different.

Each partition should have about 97656 rows. What would the metrics become now?

Since LOG(97656)/LOG(2) = 16.575421065795, a BTREE index with 1024 keys per page treenode would have a tree height of only 2 (CEILING(LOG(97656)/LOG(1024))). With only two pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 20 keys.

CONCLUSION

Spreading out the keys just removes one tree level but essentially creates 1024 indexes. The queries won't know the difference. The search time would probably be nominal at best in favor of partitions. However, make sure all the data is active. Otheriwse, you may be hitting just a few partitions, while other partitions with rarely-accessed data just takes up space and are never accessed frequently enough to justify the partitioning. You may have different performance metrics to worry about that are more blatent (such as internal defragmentation in XFS, ext3 vs ext4, etc.) You also need to worry about which storage engine you are using because:

• InnoDB indexing would be a little messier in comparison to MyISAM because of having to manage a clustered index
• InnoDB does double writing of data in ibdata1 as well as the current log file (ib_logfile0 or ib_logfile1)