Mysql currently does not support conditional indexes.
The best technical repair1 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 triggers2, 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".
1. gambiarra
2. wheat dishes not included.
If your concern is only about using resources, you don’t have much to do, since Mysql doesn’t support this type of index. If there is concern about performance, it may be possible to optimize the queries, but this will depend on analysis of the specific implementation plans.
– bfavaretto