MySQL simulated conditional Index

We know that MySQL does not support conditional indexes. What is conditional index? A conditional index is an index generated after a certain filter is performed on the index Column Based on the WHERE condition. Such indexes have the following advantages:
?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<Strong> Table "ytt. girl1" Column | Type | Modifiers -------- + --------- + -------------------- Id | integer | not null Rank | integer | not null default 0 Indexes: "Girl1_pkey" primary key, btree (id) "Idx_girl1_rank" btree (rank) WHERE rank> = 10 AND rank <= 100   The query statement is: Select * from girl1 where rank between 20 and 60 limit 20;   Query plan with all indexes: QUERY PLAN Else --------------------------------------------------------------------------------------------------------------------------------- Limit (cost = 0. 29 .. 36.58 rows = 20 width = 8) (actual time = 0. 024 .. 0.054 rows = 20 loops = 1) -> Index Scan using idx_girlw.rank on girl1 (cost = 0. 29 .. 421.26 rows = 232 width = 8) (actual time = 0. 023 .. 0.044 rows = 20 loops = 1) Index Cond: (rank> = 20) AND (rank <= 60 )) Total runtime: 0.087 MS (4 rows)   Time: 1.881 MS Query plan with conditional index:   QUERY PLAN Else --------------------------------------------------------------------------------------------------------------------------------- Limit (cost = 0. 28 .. 35.54 rows = 20 width = 8) (actual time = 0. 036 .. 0.068 rows = 20 loops = 1) -> Index Scan using idx_girlw.rank on girl1 (cost = 0. 28 .. 513.44 rows = 291 width = 8) (actual time = 0. 033 .. 0.061 rows = 20 loops = 1) Index Cond: (rank> = 20) AND (rank <= 60 )) Total runtime: 0.106 MS (4 rows)   Time: 0.846 ms </strong> <Strong> ytt> show create table girl1_filtered_index; + -------------------- + hour tables + | Table | Create Table | + ---------------------- + hour ------------------------------------------------------------------------------------ California + | girl1_filtered_index | create table 'girl1 _ filtered_index '('id' int (11) not null, 'rank' int (11) not null default '0 ', primary key ('id'), KEY 'idx _ rank '('rank') ENGINE = InnoDB default charset = latin1 | + -------------------- + bytes ------------------------------------------------------------ Rows --- + 1 row in set (0.00 sec) Next, modify the basic table update operation and create three triggers. DELIMITER $ USE't _ girl $ drop trigger /*! 50032 if exists */'filtered _ insert' $ CREATE /*! 50017 DEFINER = 'root' @ 'localhost' */TRIGGER 'filtered _ insert' AFTER insert ON 'girl1' for each row begin if new. rank BETWEEN 10 AND 100 then insert into girl1_filtered_index VALUES (new. id, new. rank); end if; END; $ DELIMITER; DELIMITER $ USE't _ girl '$ drop trigger /*! 50032 if exists */'filtered _ Update' $ CREATE /*! 50017 DEFINER = 'root' @ 'localhost' */TRIGGER 'filtered _ Update' AFTER update ON 'girl1' for each row begin if new. rank BETWEEN 10 AND 100 then replace girl1_filtered_index VALUES (new. id, new. rank); else delete from girl1_filtered_index WHERE id = old. id; end if; END; $ DELIMITER; DELIMITER $ USE't _ girl '$ drop trigger /*! 50032 if exists */'filtered _ delete' $ CREATE /*! 50017 DEFINER = 'root' @ 'localhost' */TRIGGER 'filtered _ delete' AFTER delete ON 'girl1' for each row begin delete from girl1_filtered_index WHERE id = old. id; END; $ DELIMITER; OK. Import the test data. Ytt> load data infile 'girl1.txt 'into table girl1 fields terminated by', '; Query OK, 100000 rows affected (1.05 sec) Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0 ytt> select count (*) from girl1; + ---------- + | count (*) | + ---------- + | 100000 | + ---------- + 1 row in set (0.04 sec) ytt> select count (*) from girlw.filtered_index; + ---------- + | count (*) | + ---------- + | 640 | + ---------- + 1 row in set (0.00 sec) </strong> <Strong> select. id,. rank from girl1 as a where. id in (select B. id from girl1_filtered_index as B where B. rank between 20 and 60) limit 20; </strong>