How to create a conditional index in MySQL?

Question:

How to create an index filtered by a specific range of data?

As far as I know, it's impossible to do this directly in MySQL. On some other database systems there is usually a WHERE clause for this filter.

Some systems use another name for this type of index: partial index or function-based index or filtered index .

For example: I want to create an index that only contains keys for rows with a specific characteristic, something like status = 'ATIVO' .

I can have a very large table and for certain frequent queries that certainly only need the "live" data and that's not such a large portion of the table. In this case having a keyed index for every row in the table would be a waste of resources.

The example cited is a simple expression, but it could use any other expression.

If nothing like this is possible, are there alternatives to have the index more efficiently?

Answer:

MySQL currently does not support conditional indexes.

The best technical fix 1 I see for this specific case is to create an auxiliary table with the following characteristics:

CREATE TABLE  `meu_schema`.`tabela_auxiliar` (
   `id` int unsigned NOT NULL,
   PRIMARY KEY (`id`)
);

In the main table you add three triggers 2 , with the following specification:

delimiter //

CREATE TRIGGER exemplo_parcial_insert AFTER INSERT ON tabela_principal
FOR EACH ROW
BEGIN
   IF NEW.status = 'ATIVO' THEN
      REPLACE tabela_auxiliar SET tabela_auxiliar.id = NEW.id;
   END IF;
END;//

CREATE TRIGGER exemplo_parcial_update AFTER UPDATE ON tabela_principal
FOR EACH ROW
BEGIN
   IF NEW.status = 'ATIVO' THEN
      REPLACE tabela_auxiliar SET tabela_auxiliar.id = NEW.id;
   ELSE
      DELETE FROM tabela_auxiliar WHERE tabela_auxiliar.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER exemplo_parcial_delete AFTER DELETE ON tabela_principal
FOR EACH ROW
BEGIN
   DELETE FROM tabela_auxiliar WHERE tabela_auxiliar.id = OLD.id;
END;//

delimiter ;

The use of the // delimiter is necessary as we want the ; are part of the triggers .

Thus, automatically the auxiliary table will have exactly the IDs corresponding to the entries whose status is the string "ACTIVE", being updated by triggers under normal conditions of use.

If you want, in addition to filtering, to index by a specific column , add it to the auxiliary table with a simple index, and in the triggers replace .

To use this auxiliary table in a select, just a conventional join :

SELECT * FROM tabela_auxiliar LEFT JOIN tabela_principal
   ON tabela_principal.id = tabela_auxiliar.id;

Obvious that if the main table already has data, you must fill the auxiliary table with the IDs that reach the desired condition. For this, just use the query below once:

INSERT INTO tabela_auxiliar SET id = tabela_principal.id
   WHERE tabela_principal.status="ATIVO";

As for the performance, it will depend on the tests case by case, and the amount of positive detections for the desired condition. This solution makes more sense in the case of small portions of positive results. In practice, just really testing to see if you are actually saving some space, and if the drop in performance makes up for the "juggling".

1. workaround
2. wheat dishes not included.

Scroll to Top