MySQL: How to create a partial index on a 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've these queries:

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

Can one create a partial index like (PostgreSQL-style) ?

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

__

Plan B: Having a table "winners" (prize_id, participant_id).

Answer 1

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

If people refer to "partial indexes" in MySQL, they're referring to text "prefix indexes" where only a certain number of leading characters are indexed rather than the whole value. For example, an index could be made by only indexing the first 5 characters.

If you're looking for a performance boost because it's not using both win and prize_id columns (though I see you don't currently have win indexed), you could create a composite index over (win, prize_id) (in that order) so that it would filter values by win first, then look at the remaining prize_id value.

If you're instead looking for a partial index simply for the purposes of decreasing size/memory usage of that index, this would unfortunately have the opposite effect.