To have PostgreSQL-like Partial Index in MySQL 5.5

Question

I have big data where I only select a small interval of data at a time such that the selection is always in a sequence. I am trying to implement PostgreSQL like Partial index in MySQL which is targeted for such purposes. I am not sure if the partial unique constraint is the same as the one which I want.

Code in PostgreSQL 9.4

CREATE UNIQUE INDEX dir_events
    ON events (measurement_id)
    USING btree
    (eventBody)
    WHERE is_active;

Attempt on ypercube's partial index in MySQL

CREATE UNIQUE INDEX dir_events
    [index_type] -- TODO what here?
    ON events (measurement_id, is_active)
    [index_type] -- TODO what here?

How can you create PostgreSQL-like partial index in MySQL 5.5 or similar?

Answer 1

Neither MySQL nor the siblings (MariaDB, Drizzle, etc) have implemented partial indexes.

What you can do, with this restriction in mind:

• a) make a simple (not partial) index on (is_active, measurement_id). It will be used in queries where the partial index would. Of course if the is_active column is 3% True and 97% false, this index will be much bigger (than a partial index). But still smaller than the table and useful for these queries.
  Another limitation is the index cannot be UNIQUE with this solution so the constraint is not enforced. If the index is created with UNIQUE, the uniqueness will be enforced for rows with is_active = FALSE as well. I assume you don't want that:

  CREATE INDEX dir_events
      ON events (is_active, measurement_id)
      USING btree ;
  

• b1) (the simple variation of b): add another table in your design, with only the primary key columns of events and a foreign key to events. This table should only have rows where the is_active is true in the original table (this will be enforced by your application/procedures). Queries with is_active = TRUE would be changed to join to that table (instead of the WHERE condition.)
  The UNIQUE is not enforced either with this solution but the queries would only do a simple join (to a much smaller index) and should be quite efficient:

  CREATE TABLE events_active
  ( event_id INT NOT NULL,         -- assuming an INT primary key on events
    PRIMARY KEY (event_id),
    FOREIGN KEY (event_id)
      REFERENCES events (event_id)
  ) ;
  
  INSERT INTO events_active 
    (event_id)
  SELECT event_id
  FROM events
  WHERE is_active = TRUE ;
  

• b2) a more complex solution: add another table in your design, with only the primary key columns of the table and measurement_id. As in previous suggestion, this table should only have rows where the is_active is true in the original table (this will be enforced by your application/procedures, too). Then use this table only instead for queries that have WHERE is_active = TRUE and need only the measurement_id column. If more columns are needed from events, you'll have to join, as before.
  The UNIQUE constraint can be enforced with this solution. The duplication of measurement_id column can also be secured to be consistent (with an extra unique constraint on events and a composite foreign key):

  ALTER TABLE events
    ADD UNIQUE (event_id, measurement_id) ;
  
  CREATE TABLE events_active
  ( event_id INT NOT NULL,
    measurement_id INT NOT NULL.
    PRIMARY KEY (event_id, measurement_id),
    UNIQUE (measurement_id),
    FOREIGN KEY (event_id, measurement_id)
      REFERENCES events (event_id, measurement_id)
  ) ;
  
  INSERT INTO events_active 
    (event_id, measurement_id)
  SELECT event_id, measurement_id
  FROM events
  WHERE is_active = TRUE ;
  

• c) maybe the simplest of all: use PostgreSQL. I'm sure there are packages for your Linux distribution. They may not be the latest version of Postgres but partial indexes were added in 7.0 (or earlier?) so you shouldn't have a problem. Plus, I'm confident that you could install the latest version in almost any Linux distribution - even with a little hassle. You only need to install it once.

Answer 2

It is not ideal, but if you have validation on the field, you can make a change that makes the value invalid. For example illegal characters, or negative numbers. You can make this change when soft deleting and you know it will not clash with a valid value. You also need to watch for soft deleted values not clashing with each other as well.

In 1 case, I had a email column with a unique constraint and a autoincrement integer id for each row. On soft delete, I added "id@", where id was the unique row ID, before the real email. @ is not allowed in emails unless it is quoted, so I know no valid email will clash with the new value, and so this will never clash with a valid email. The unique integer ID also guarantees each deleted row will be unique, even if the same email is deleted multiple times.

I know this is not ideal, but it is a simple way to work around the issue.

NOTE: The change I mention adds characters to the unique field, so I had to do additional tricks to if the current value is already at/near the max length. They are application specific, so not worth mentioning here, but be aware and come up with a workaround for that too and this is a simple way to work around missing the partial index feature.