mysql tinyint create,MySQL：如何在boolean / tinyint列上创建部分索引？

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).

解决方案

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.