MySQL Simulation condition index

We know that MySQL does not support conditional indexing. What is a conditional index? A conditional index is an index that is produced after a certain filter on an indexed 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 >= and rank <=   Execute the query statement as: SELECT * from GIRL1 WHERE rank between 20 Limit 20;   query plan with all indexes:                                                               query plan                                                             -- --------------------------------------------------------------------------------------------------------------- ---------------- Limit  (cost=0.29..36.58 rows=20 width=8) (actual time=0.024..0.054 rows=20 Loops=1)     ->  Index Scan using Idx_girl1_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 >=) and ( Rank <=)  total runtime:0.087 ms (4 rows)   time:1.881 MS query plan with conditional index:       & nbsp;                                                        query plan                                                              -------------------------------------- ------------------------------------------------------------------------------------------- Limit  ( cost=0.28..35.54 rows=20 width=8) (actual time=0.036..0.068 rows=20 Loops=1)    ->  Index Scan Using Idx_girl1_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 >=) and (rank <=)  total runtime:0. (4 rows)   time:0.846 ms</strong> <strong>ytt>show CREATE TABLE Girl1_filtered_index; +----------------------+--------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------------+--------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------+ | Girl1_filtered_index | CREATE TABLE ' Girl1_filtered_index ' (' id ' int () not null, ' rank ' int (one) not null DEFAULT ' 0 ', PRIMARY key (' ID '), key ' Idx_rank ' (' rank ') Engine=innodb DEFAULT charset=latin1 | +----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---+ 1 row in Set (0.00 sec) Next, the update operation on the underlying table is modified to create three triggers. DELIMITER $$ use ' t_girl ' $$ DROP TRIGGER/*!50032 IF EXISTS/' Filtered_insert ' $$ CREATE/*!50017 definer = ' root ' @ ' local Host ' */TRIGGER ' Filtered_insert ' after insert on ' GIRL1 ' for each ROW BEGIN IF new.rank BETWEEN and THEN insert I NTO 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 ' @ ' local Host '/TRIGGER ' filtered_update ' after update on ' GIRL1 ' for each ROW BEGIN IF new.rank BETWEEN and 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 ' @ ' local Host ' */TRIgger ' Filtered_delete ' after delete on ' girl1 ' for each ROW BEGIN delete from girl1_filtered_index WHERE id = old.id; End; $$ DELIMITER; OK, let's import the test data. Ytt>load data infile ' girl1.txt ' into the 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 GI RL1; +----------+ | COUNT (*) | +----------+ | 100000 | +----------+ 1 row in Set (0.04 sec) ytt>select count (*) from Girl1_filtered_index; +----------+ | COUNT (*) | +----------+ | 640 | +----------+ 1 row in Set (0.00 sec) </strong> <strong>select A.id,a.rank from Girl1 as a where a.id in (select b.ID from Girl1_filtered_index as B where b.rank between) limit 20;</strong>