Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL5.7 implement virtual column expression index     - GAMIT baseline solution using batch were counted (Linux)

- Let the router be your security barrier against external attack (Linux)

- Oracle common internal event tracking number (Database)

- Git build a team development environment operating drills (Linux)

- Ubuntu install Eclipse can not find JAVA_HOME problem (Linux)

- ethtool command Detailed (Linux)

- Linux folder and extract the differential file comparison techniques -rsync Magical (Linux)

- Spark build standalone version cluster (Server)

- Open SSH remote access service that allows Android phone Ubuntu 14.04 (Linux)

- Talk about jsonp (Programming)

- MySQL appears data too long for column 'name' at row 1 to solve (Database)

- Install Xshell on Mac OS X (Linux)

- Using DBMS_STAT function closes mission (Database)

- Use libpq under Ubuntu 14.04 (Linux)

- Let OpenCV face detection score output codes (Programming)

- To build PHP environment (Nginx + MariaDB + PHP7) under CentOS 6.5 (Server)

- File sharing and fork function (Programming)

- MySQL completely uninstall and install Configuring Character Sets under Linux (Database)

- Manually create Oracle Database Explanations (Database)

- Nine artifact control disk partition under Linux (Linux)

 
         
  MySQL5.7 implement virtual column expression index
     
  Add Date : 2018-11-21      
         
         
         
  MySQL does not provide a functional index since ancient times such complex functions. That how to achieve this function in MySQL inside? Let's look at the concept of a function of the index. Functional index, also known as an expression index, which is the field index to a particular function (expression) based on the need to improve the performance of the query. Advantages functional index is more accurate to obtain the required data.

MySQL 5.7 offers a new feature, a virtual column, can be the perfect solution to this problem.

Before introducing virtual column, we look at the example in which the general index of MySQL.

Example table structure:

CREATE TABLE t1 (id INT, rank INT, log_time DATETIME, nickname VARCHAR (64)) ENGINE INNODB;
ALTER TABLE t1 ADD PRIMARY KEY (id), ADD KEY idx_rank (rank), ADD KEY idx_log_time (log_time);

Sample data table, where I added the 5000 record:

mysql> select count (*) from t1;
+ ---------- +
| Count (*) |
+ ---------- +
| 5000 |
+ ---------- +
1 row in set (0.00 sec)

 Suppose we retrieve April 9, 2015 data. (As a result there are two records, id were 95 and 3423.)

mysql> SELECT * FROM t1 WHERE DATE (log_time) = '2015-04-09' \ G
*************************** 1. row ******************** *******
      id: 95
    rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
*************************** 2. row ******************** *******
      id: 3423
    rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
2 rows in set (0.01 sec)

Next we look at the query plan of this statement.

mysql> explain SELECT * FROM t1 WHERE DATE (log_time) = '2015-04-09' \ G
*************************** 1. row ******************** *******
          id: 1
  select_type: SIMPLE
        table: t1
  partitions: NULL
        type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
        rows: 5000
    filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

We found TYPE is ALL, the scan function 5000, that this statement was a full table scan. Although the field log_time added to the index, but not used, then how to do this time?

In general MySQL inside this Review:

mysql> SELECT * FROM t1 WHERE log_time> = '2015-04-09 00:00:00' AND log_time <= '2015-04-10 00: 00: 00' \ G
*************************** 1. row ******************** *******
      id: 3423
    rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
*************************** 2. row ******************** *******
      id: 95
    rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
2 rows in set (0.00 sec)

Query results, findings consistent set, that look at the query plan

mysql> explain SELECT * FROM t1 WHERE log_time> = '2015-04-09 00:00:00' AND log_time <= '2015-04-10 00: 00: 00' \ G
*************************** 1. row ******************** *******
          id: 1
  select_type: SIMPLE
        table: t1
  partitions: NULL
        type: range
possible_keys: idx_log_time
          key: idx_log_time
      key_len: 6
          ref: NULL
        rows: 2
    filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

You can see this modified statement to idx_log_time good use of this index.

 

Well, this is before the older MySQL 5.6 and previous versions of the solution, along with the release of MySQL 5.7, the emergence of virtual column should make this easier.

Now change before the next table structure:

ALTER TABLE t1 ADD COLUMN log_date DATE AS (DATE (log_Time)) stored, ADD KEY idx_log_date (log_date);

Thus, adding a new column to date (log_time) This expression is stored, and gave him an extra one index.

So, before the statement becomes as follows:

mysql> SELECT * FROM t1 WHERE log_date = '2015-04-09' \ G
*************************** 1. row ******************** *******
      id: 95
    rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
log_date: 2015-04-09
*************************** 2. row ******************** *******
      id: 3423
    rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
log_date: 2015-04-09
2 rows in set (0.00 sec)

Results of previous sets and consistent execution.

 

Let's look at the query plan, we found good use idx_log_date index column.

mysql> explain SELECT * FROM t1 WHERE log_date = '2015-04-09' \ G
*************************** 1. row ******************** *******
          id: 1
  select_type: SIMPLE
        table: t1
  partitions: NULL
        type: ref
possible_keys: idx_log_date
          key: idx_log_date
      key_len: 4
          ref: const
        rows: 2
    filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Through the above description, we see the virtual columns to implement relative to the previous method of easier. But here I'd have to say a few words.

Function index usage, and to write SQL statements, although simple, but in most cases, can only be a last resort, one design flaw is on the increase in the latter part of the operation and maintenance of complicated and difficult operation and maintenance personnel. This is why MySQL 5.7 was introduced until the reasons for this similar feature.
     
         
         
         
  More:      
 
- Linux yum command Detailed (Linux)
- Create the best introductory tutorial REST API (Linux)
- Linear table with a Java implementation of the iterator (Programming)
- Use PuTTY key authentication mechanism for remote login Linux (Linux)
- Oracle Database routine inspection (Database)
- floating IP in OpenStack neutron (Server)
- Get and Post requests Comments (Linux)
- Ubuntu and Derivative Edition users install LMMS 0.4.15 (Linux)
- CentOS 6.4 Python 2.6 upgrade to 2.7 (Linux)
- Linux operating system ARP Spoofing Defense (Linux)
- DB2 Version SQLJ to access Oracle Server (Database)
- grep regular expression (Linux)
- Availability Hadoop platform - Oozie Workflow (Server)
- The principle Httpclient4.4 (HttpClient Interface) (Programming)
- Use Tails 1.4 Linux system to protect the privacy and anonymity (Linux)
- Intruder tools Knark Analysis and Prevention Linux environment (Linux)
- Let you Fun Ubuntu desktop eleven weapon (Linux)
- The difference between Linux su and sudo commands (Linux)
- How to create SWAP files in Ubuntu 14.04 (Linux)
- Difference LVS three scheduling modes (Server)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.