MySQL simulated conditional Index

StrongTableytt. girl1Column | Type | Modifiers -------- + --------- + -------------------- id | integer | notnullrank | integer | Random: Random, btree (id) random (rank) WHERErank10AN

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

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

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, let's 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)

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 20 and 60) limit 20;