How to create a conditional index in MySQL?


How to create a filtered index for a specific range of data?

As far as I know, it is 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 < a href=""> role-based index or even 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 "active" data and that are not such a large portion of the table. In this case having an index with keys for all rows of the table would be a waste of resources.

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

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

asked by anonymous 23.01.2014 / 03:58

3 answers


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
   IF NEW.status = 'ATIVO' THEN
      REPLACE tabela_auxiliar SET =;
   END IF;

CREATE TRIGGER exemplo_parcial_update AFTER UPDATE ON tabela_principal
   IF NEW.status = 'ATIVO' THEN
      REPLACE tabela_auxiliar SET =;
      DELETE FROM tabela_auxiliar WHERE =;
   END IF;

CREATE TRIGGER exemplo_parcial_delete AFTER DELETE ON tabela_principal
   DELETE FROM tabela_auxiliar WHERE =;

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 =;

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

23.01.2014 / 05:55

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.

    count(LEFT(status, 1)) as contagem_usuarios, 
    LEFT(status, 1) as status 
LEFT(status, 1)

2) Getting all Inactive users:

    LEFT(status,1) = 'I'
23.01.2014 / 04:14

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.

10.04.2018 / 21:45