Create Index for specific value in mysql column

Question

I have a column in my table with TinyInt type, which stores millions of numbers ranging from 0 to 50.

However a great proportion of them have the exact value of 50.

Now I need to set an index to split 50s and non 50s. I have already a BTREE index but it is not used in queries like this

SELECT * FROM table WHERE num != 50

I think a BTREE index is useful for range indexes. But I need an index for an exact value only.

• Akina: Use partitioning. Or at least test .. WHERE num < 50. Also you may try to force the index usage.

Answer 1

OPTION #1

ALTER TABLE mytable ADD INDEX mycolumn_index (mycolumn);

Then, just run this

SELECT * FROM table WHERE num < 50;

IMHO it would produce a very lopsided index because of the majority of the values being 50. It would just be a waste of space.

OPTION #2 : Partition the table

ALTER TABLE mytable
PARTITION BY RANGE (mycolumn) (
 PARTITION p0 VALUES LESS THAN (50),
 PARTITION p1 VALUES LESS THAN (51),
 PARTITION p2 VALUES LESS THAN MAXVALUE
);

What does this produce ???

• All rows with mycolumn < 50 land in partition p0.
• All rows with mycolumn = 50 land in partition p1.
• All rows with mycolumn > 50 land in partition p2.

Partition p2 would never grow if you never enter data with values > 50. Otherwise, that partition will have 0 rows and may occupy like 4K at best.

Then you could run the SELECT against all the non-50 values like this

SELECT * FROM employees PARTITION (p0);

This would be a very quick table scan on just that partition.

This query

SELECT * FROM table WHERE num < 50;

would still work just as well with an index.

• Rick James: ("num" vs "mycolumn" -- Please be consistent.)