Experience MySQL's index to improve search performance

1, create a user table with two columns Name,phone

2. Insert 100W Records in Python (any language you like) (LZ's notebook is older, about 1 minutes):

#!/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) C6/>sql = "INSERT into user (Name,phone) VALUES ('%s ', '%s ')"% (Uname,uphone)    cur.execute (SQL) Conn.commit () Cur.close () Conn.close ()

3, search without indexing:

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 |
+-------------+
| 18800055555 |
+-------------+
1 row in Set (0.46 sec)

4, index The Name property:

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= ' user999999 ';
+---------+------------+--------------+
| UID | name | Phone |
+---------+------------+--------------+
| 1000011 | user999999 | 188000999999 |
+---------+------------+--------------+
1 row in Set (0.00 sec)

Result seconds out. It can be seen that the index has a great increase in search performance on a database with large amounts of data.