How to create a conditional index in Mysql?

Question

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

As far as I know, it is impossible to do this directly in Mysql. In 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 lines with a specific feature, something like status = 'ATIVO'.

I can have a very large table and for certain darlings frequent that certainly only need the "active" data and that are not such a large portion of the table. In this case having an index with keys for all table rows would be a waste of resources.

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

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

Answer 1

Mysql currently does not support conditional indexes.

The best technical repair¹ that 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², 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 delimiter // is necessary, because we want ; 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, indexing by a specific column, add it to the auxiliary table with simple index, and replace of triggers.

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

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

Obviously, if the main table already has data, you must fill the auxiliary table with the Ids that reach the desired condition. To do this, just use the query below once:

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

As for performance, it will depend on the tests on a case-by-case basis, and the amount of positive detections for the desired condition. This solution makes more sense in the case of small plots of positive results. In practice, just testing even to see if it is actually saving some space, and if the performance drop compensates the "juggling".

<sub>1. gambiarra
2. wheat dishes not included.</sub>

Answer 2

Mysql does not support conditional indices, however, the term índices parciais is generally found in the literature referring to the index of prefixo, which is a resource supported by Mysql, see the example:

We have a table, named after usuarios, and a column with a name status of the kind varchar size 10

CREATE INDEX indice_prefixo_status ON usuarios (status(1));

This will create an index that will index only the first character, thus saving an immense amount of resources.

When Query, for this index to be used, we should use some string manipulation function, such as LEFT or SUBSTR, see examples:

1) Getting the number of active users / Inactive using the function LEFT.

SELECT 
    count(LEFT(status, 1)) as contagem_usuarios, 
    LEFT(status, 1) as status 
FROM 
    usuarios 
GROUP_BY
LEFT(status, 1)

2) Getting All Inactive Users:

SELECT
    *
FROM
    usuarios
WHERE
    LEFT(status,1) = 'I'

Answer 3

I think you will get a better performance by adding a partition to the table.

CREATE TABLE sua_tabela (campos ..) PARTITION  BY LIST(status) ( PARTITION p_ativos VALUES IN ('ATIVO'), PARTITION p_inativos VALUES IN ('INATIVO')); 

This procedure physically divides the table.