We know that MySQL currently does not support function indexing. Currently, most databases include PostgreSQL and Oracle. What is a function index? T_girl # demail_list; Tablepublic. email_listColumn | Type | Modifiers ---------- + ----------------------------- + ----------- id | intege
We know that MySQL currently does not support function indexing. Currently, most databases include PostgreSQL and Oracle. What is a function index? T_girl = # \ d email_list; Table public. email_list Column | Type | Modifiers ---------- + ------------------------------- + ----------- id | intege
We know that MySQL currently does not support function indexing. Currently, most databases include PostgreSQL and Oracle. What is a function index?
T_girl = # \ d email_list; Table "public. email_list "Column | Type | Modifiers ---------- + ----------------------------- + ----------- id | integer | email | character varying (200) | log_time | timestamp without time zone | Indexes: "idx_email_suffix" btree (substr (email: text, "position" (email: text, '@': text) + 1 ))
T_girl = # select count (*) from email_list; count -------- 200000 (1 row) Time: 39.851 MS
Query plan certificate ------- Aggregate (cost = 1607. 19 .. 1607.20 rows = 1 width = 12) (actual time = 5. 514 .. 5.514 rows = 1 loops = 1)-> Bitmap Heap Scan on email_list (cost = 48. 29 .. 1602.08 rows = 2047 width = 12) (actual time = 1. 126 .. 4.806 rows = 1960 loops = 1) Recheck Cond: (substr (email): text, ("position" (email): text, '@': text) + 1) = '56. com ': text)-> Bitmap Index Scan on idx_email_suffix (cost = 0. 00 .. 47.78 rows = 2047 width = 0) (actual time = 0. 802 .. 0.802 rows = 1960 loops = 1) Index Cond: (substr (email): text, ("position" (email): text, '@': text) + 1) = '56. com ': text) Total runtime: 5.603 MS (6 rows) Time: 6.601 MS
T_girl = # select count (email) as num from email_list where substr (email, position ('@' in email) + 1) = '56. com '; num ------ 1960 (1 row) Time: 5.251 MS t_girl = #
MariaDB [t_girl]> show create table email_list; + ------------ + hour ------------------------------------------- + | Table | Create Table | + ------------ + hour + | email_list | create table 'email _ List' ('id' int (11) default null, 'email 'varchar (200) default null, 'Log _ time' datetime (6) default null, 'email _ suffix' varchar (100) AS (substr (email, position ('@' in email) + 1) PERSISTENT, KEY 'idx _ email_suffix '('email _ suffix ')) ENGINE = InnoDB default charset = latin1 | + ------------ + zookeeper ------------------------------------------------- + 1 row in set (0.01 sec)
MariaDB [t_girl]> explain select count (email) from email_list where email_suffix = '56. com '; + ------ + ------------- + ------------ + ------ + upper + ------------------ + --------- + ------- + ------ + upper + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ------ + ------------- + ------------ + ------ + ------------------ + ---------------- + --------- + ------- + ------ + upper + | 1 | SIMPLE | email_list | ref | cursor | percent | 103 | const | 1959 | Using index condition | + ------ + ------------- + ------------ + ------ + ------------------ + --------- + ------- + ------ + ------------------------- + 1 row in set (0.02 sec)
MariaDB [t_girl]> select count(email) from email_list where email_suffix = '56.com'; +--------------+| count(email) |+--------------+| 1960 |+--------------+1 row in set (0.02 sec)
