How to create a conditional index in MySQL?

MySQL currently does not support conditional indexes.

The best technical 1 I see for this particular 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 delimiter // is required since we want ; to be part of triggers .

So, 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 usage conditions.

  

If you'd like to in addition to filtering, indexing by some specific column , add it to the auxiliary table with simple index, and the% of triggers.

To use this auxiliary table in a select, just a replace 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 in the auxiliary table with the IDs that reach the desired condition. To do this, simply use the query below once:

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

Regarding performance, it will depend on the case-by-case tests, 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 to see if it's really saving some space, and if the performance drop compensates for "juggling."

1. gambiarra
2. wheat dishes not included.

MySQL does not support conditional indexes, however, the term índices parciais is generally found in the literature referring to the prefixo index, which is a feature supported by MySQL, see the example:

We have a table named usuarios , and a column, named status of type varchar size 10

  

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

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

When doing the query, for this index to be used, we must use some string manipulation function, such as LEFT or SUBSTR , see examples :

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

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'

I think you'll have 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.