Database index operations

Database index operations have never used indexes before and fail to understand the benefits of indexes. A few days ago, we had to process tables with millions of records, such as queries on both sides, with more records, the query speed is quite slow, so I tried to use the index and found that the speed increase is not a little bit, but a lot of points. The procedure is as follows: 1. Create two tables first, as shown in: The table creation statement is as follows:

[sql] CREATE TABLE `t1` (    `ID` bigint(20) NOT NULL AUTO_INCREMENT,    `Num` int(11) NOT NULL,    PRIMARY KEY (`ID`)  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;  

 

Table 2 and table 1 are similar to table 2 and table 2. Data is filled with the stored procedure. The steps are as follows:

The above is the interface for creating a stored procedure with navicat. Two stored procedures are created to fill data in t1 and t2 respectively. The Code is as follows:

[SQL] BEGIN # Routine body goes here... declare n int; set n = 1; while n <= 50000 do insert into t1 (Num) values (n); set n = n + 1; end while; storage Process of END filling t2 [SQL] BEGIN # Routine body goes here... declare n int; set n = 1; while n <= 25000 do insert into t2 (Num) values (n); set n = n + 1; end while; set n = 1; while n <= 25000 do insert into t2 (Num) values (0); set n = n + 1; end while; END

 

3. Perform a join Table query to find the rows with the same Num field in the two tables. The query statement is as follows:

[sql] select count(*) from t1,t2 where t1.Num = t1.Num;  

 

Each line of t1 needs to be compared with each line of t2, that is to say, t2 needs to be scanned 50000 times from start to end. If the data is bigger, the patient cannot wait, so you have to find a solution. Here you can use indexes. I will not talk about the benefits of creating an index here. I just want to talk about how to use an index. In fact, it is very simple. You don't need to change anything else. You only need to do two more things before querying: first, create an index for t1; second, create an index for t2; and the statement for creating an index is:

[sql] create index Num_i1 on t1(Num);   create index Num_i2 on t2(Num);  

After executing the same query statement, it would take a few minutes to run without an index on my computer. After the index is added, the results will be displayed in the twinkling of an eye. Therefore, when the data volume is large, you must remember to use indexes. If there are multiple filtering conditions, you can also index multiple columns. For example

create index Num_i1 on t1(ID,Num);