Jak utworzyć indeks warunkowy w MySQL?

Jak utworzyć indeks do filtrowania określonego zakresu lub podzbioru tabeli w MySQL? AFAIK nie można utworzyć bezpośrednio, ale myślę, że można symulować tę funkcję.

Przykład: Chcę utworzyć indeks dla NAMEkolumny tylko dla wierszy zSTATUS = 'ACTIVE'

Ta funkcja nazywa się indeksem filtrowanym w programie SQL Server i indeksem częściowym w Postgres.

MySQL obecnie nie obsługuje indeksów warunkowych.

Aby osiągnąć to, o co prosisz (nie to, że powinieneś to zrobić;)) możesz zacząć tworzyć tabelę pomocniczą:

CREATE TABLE  `my_schema`.`auxiliary_table` (
   `id` int unsigned NOT NULL,
   `name` varchar(250), /* specify the same way as in your main table */
   PRIMARY KEY (`id`),
   KEY `name` (`name`)
);

Następnie dodajesz trzy wyzwalacze w głównej tabeli:

delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   END IF;
END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   ELSE
      DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
   DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
END;//

delimiter ;

Potrzebujemy, delimiter //ponieważ chcemy używać ;wewnątrz wyzwalaczy.

W ten sposób tabela pomocnicza będzie zawierać dokładnie identyfikatory odpowiadające głównym wierszom tabeli zawierającym ciąg „AKTYWNY”, aktualizowanym przez wyzwalacze.

Aby użyć tego na a select, możesz użyć zwykłego join:

SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table
   ON auxiliary_table.id = main_table.id
   ORDER BY auxiliary_table.name;

Jeśli główna tabela już zawiera dane lub jeśli wykonasz operację zewnętrzną, która zmienia dane w nietypowy sposób (np. Poza MySQL), możesz naprawić tabelę pomocniczą w ten sposób:

INSERT INTO auxiliary_table SET
   id = main_table.id,
   name = main_table.name,
   WHERE main_table.status="ACTIVE";

Jeśli chodzi o wydajność, prawdopodobnie będziesz mieć wolniejsze wstawianie, aktualizowanie i usuwanie. Może to mieć sens tylko wtedy, gdy naprawdę masz do czynienia z kilkoma przypadkami, w których pożądany stan jest pozytywny. Nawet w ten sposób, prawdopodobnie tylko testy pozwalają sprawdzić, czy zaoszczędzone miejsce naprawdę uzasadnia to podejście (i czy naprawdę oszczędzasz w ogóle miejsce).

Jeśli dobrze rozumiem pytanie, myślę, że to, co osiągniesz, to utworzenie indeksu dla obu kolumn, NAZWA i STATUS. Pozwoliłoby to skutecznie zapytać, gdzie NAME = „SMITH” i STATUS = „ACTIVE”

Nie możesz wykonywać indeksowania warunkowego, ale na przykład możesz dodać indeks wielokolumnowy na ( name, status).

Mimo że zindeksuje wszystkie dane w tych kolumnach, nadal pomoże ci znaleźć poszukiwane nazwy ze statusem „aktywne”.

Można to zrobić, dzieląc dane między dwie tabele, używając widoków do zjednoczenia dwóch tabel, gdy wszystkie dane są potrzebne, i indeksując tylko jedną tabelę w tej kolumnie - ale myślę, że spowodowałoby to problemy z wydajnością zapytań, które muszą przeglądaj całą tabelę, chyba że planista zapytań jest bardziej sprytny niż mu się wydaje. Zasadniczo należy ręcznie podzielić tabelę na partycje (i zastosować indeks tylko do jednej z partycji).

Niestety, wbudowana funkcja partycjonowania tabel nie pomoże ci w twoim zadaniu, ponieważ nie możesz zastosować indeksu do pojedynczej partycji.

Możesz utrzymać dodatkową kolumnę z indeksem i mieć wartość w tej kolumnie tylko wtedy, gdy warunek, na którym ma być oparty indeks, jest spełniony, ale jest to prawdopodobnie pracochłonne i ma ograniczoną (lub ujemną) wartość pod względem efektywność zapytań i oszczędność miejsca.

MySQL ma teraz wirtualne kolumny, których można używać do indeksów.