Experience how MySQL indexes improve search performance

Experience how MySQL indexes improve search performance 1. Create a user table that contains two columns: name and phone2. Use python (any language you like) insert records (the lz notebook is old, it takes about 1 minute ):#! Usrbinenvpython #-*-coding: UTF-8-*-importMySQLdbconnMySQLdb. connect (hos

Experience how MySQL indexes improve search performance 1. Create a user table that contains two columns: name and phone 2. Use python (any language you like) insert records (the lz notebook is old, it takes about 1 minute ):#! /Usr/bin/env python #-*-coding: UTF-8-*-import MySQLdbconn = MySQLdb. connect (hos

Experience how MySQL indexes improve search performance

1. Create a user table, which contains two columns: name and phone.

2. Use python (any language you like) to insert records (the lz notebook is old and takes about 1 minute ):

#!/usr/bin/env python# -*- coding:utf-8 -*-import MySQLdbconn =  MySQLdb.connect(host='localhost',user='root',db='millionMessage')cur = conn.cursor()for i in range(1,1000000):    uname = "user" + str(i)    uphone = "188000" + str(i)    sql = "insert into user(name,phone) values('%s','%s')" % (uname,uphone)    cur.execute(sql)conn.commit()cur.close()conn.close()

3, Search without creating an index:

Mysql> select * from user where name = 'user55555 ';
+ ------- + ----------- + ------------- +
| Uid | name | phone |
+ ------- + ----------- + ------------- +
| 55567 | user55555 | 18800055555 |
+ ------- + ----------- + ------------- +
1 row in set (0.53 sec)

Mysql> select phone from user where name = 'user55555 ';
+ ------------- +
| Phone |
+ ------------- +
| 1, 18800055555 |
+ ------------- +
1 row in set (0.46 sec)

4,Index the name attribute:

Mysql> alter table user add index index_username (name );
Query OK, 0 rows affected (22.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

5,Query:

Mysql> select * from user where name = 'user55555 ';
+ ------- + ----------- + ------------- +
| Uid | name | phone |
+ ------- + ----------- + ------------- +
| 55567 | user55555 | 18800055555 |
+ ------- + ----------- + ------------- +
1 row in set (0.00 sec)

Mysql> select * from user where name = 'user99999999 ';
+ --------- + ------------ + -------------- +
| Uid | name | phone |
+ --------- + ------------ + -------------- +
| 1000011 | user999999 | 188000999999 |
+ --------- + ------------ + -------------- +
1 row in set (0.00 sec)

Results are output in seconds. It can be seen that in databases with massive data, the index improves the search performance greatly.