MySQL: how to create partial index in boolean / tinyint column?

Question

CREATE TABLE participations ( id int(10) unsigned NOT NULL AUTO_INCREMENT, participant_id int(10) unsigned NOT NULL, prize_id int(10) unsigned NOT NULL, win tinyint(1) NOT NULL DEFAULT '0', -- ... PRIMARY KEY (id), INDEX participant_id (participant_id), INDEX prize_id (prize_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

I have these queries:

 SELECT prize_id, participant_id FROM participations WHERE win=1; SELECT participant_id FROM participations WHERE prize_id=1 AND win=1; 

Is it possible to create a partial index, for example ( PostgreSQL-style )?

 CREATE UNIQUE INDEX prize_win ON participations (prize_id) WHERE win=1; 

__

Plan B: the presence of the table "winners" (prib_name, member_id).

Answer 1

No, MySQL does not support partial indexes of this type.

If people refer to "partial indexes" in MySQL, they refer to textual "prefix indexes" where only a certain number of leading characters are indexed, not an integer value. For example, an index can only be made by indexing the first 5 characters.

If you are looking for performance improvements because it does not use win and prize_id (although I see that you currently do not have win indexing), you can create a composite index above (win, prize_id) (in that order) so that first filter the values ​​on win , and then look at the remaining prize_id value.

If you are looking for a partial index just to reduce the size / memory usage of this index, this unfortunately has the opposite effect.