Improved efficiency of using indexes for Mysql instances

The efficiency of using indexes in Mysql for instance testing increases the efficiency of DATABASE creation: [SQL] create database 'SQL _ learn_db'; www.2cto.com creates a Table: [SQL] Create Table: create table 'persons '('id' int (11) not null AUTO_INCREMENT, 'lastname' varchar (255) default null, 'firstname' varchar (255) default null, 'address' varchar (255) default null, 'city' varchar (255) default null, primary key ('id') ENGINE = InnoDB default charset = utf8 table structure: www.2cto.com [plain] + -- --------- + -------------- + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + ----------- + -------------- + ------ + ----- + --------- + ---------------- + | Id | int (11) | NO | PRI | NULL | auto_increment | LastName | varchar (255) | YES | NULL | FirstName | varchar (255) | YES | NULL | Address | varchar (255) | YES | NULL | City | varchar (255) | YES | | NULL | + ----------- + -------------- + ------ + ----- + --------- + ---------------- + insert 1000000 data records using JDBC. DBIndexTest. java: (Please manually modify the user name and password and import the driver package) [java] import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException; import java. util. random; public class DBIndexTest {private static final String MYSQL_DRIVER = "com. mysql. jdbc. driver "; private static final String DB_URL =" jdbc: mysql: // localhost: 3306/SQL _learn_db "; private static final String USE R_NAME = "root"; private static final String PASSWORD = ""; private static final String SQL = "insert into persons values (null ,?,?, 'Zjut', 'hangzhou') "; private static Connection conn = null; private static PreparedStatement pstmt = null; private static Random random = new Random (); private DBIndexTest () {}; public static String getRandomName () {int fornum = 1 + random. nextInt (10);/* 1 ~ 10 */StringBuilder sb = new StringBuilder ();/* 97 ~ 122 */for (int I = 0; I <fornum; I ++) {sb. append (char) (97 + random. nextInt (26);} return sb. toString ();} private static void createConnection () {try {Class. forName (MYSQL_DRIVER); conn = DriverManager. getConnection (DB_URL, USER_NAME, PASSWORD);} catch (ClassNotFoundException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (SQLException e) {// TODO Auto-generated catch block e. PrintStackTrace () ;}} public static Connection getConnection () {if (conn = null) {createConnection () ;}return conn ;} public static void insertRecord () {conn = getConnection (); try {if (pstmt = null) pstmt = conn. prepareStatement (SQL); pstmt. setString (1, getRandomName (); pstmt. setString (2, getRandomName (); int affect = pstmt.exe cuteUpdate (); System. out. println (affect = 1? "Inserted successfully! ":" Insertion failed! ");} Catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace () ;}} public static void main (String [] args) {long start = System. currentTimeMillis (); for (int I = 0; I <1000000; I ++) {insertRecord () ;}long end = System. currentTimeMillis (); System. out. println ("Total time:" + (end-start)/1000.0 + "s") ;}} running result: [plain] inserted successfully! Inserted successfully! Inserted successfully!... Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Inserted successfully! Total time: the number of inserted records in the 2167.19 s table: [SQL] mysql> select count (*) from persons; + ---------- + | count (*) | + ---------- + | 1000000 | + ---------- + when no index is set for the table's column LastName, try to query: [SQL] mysql> select count (*) from persons where lastname = 'abc'; + ---------- + | count (*) | + ---------- + | 7 | + ---------- + 1 row in set (6.33 sec) index the table column LastName: [SQL] mysql> create index my_index on persons (lastname); Query OK, 0 rows affec Ted (12.44 sec) Records: 0 Duplicates: 0 Warnings: 0 run the same query again: www.2cto.com [SQL] mysql> select count (*) from persons where lastname = 'abc '; + ---------- + | count (*) | + ---------- + | 7 | + ---------- + 1 row in set (0.00 sec) Of course, 0.00 sec is not equal to 0, because the speed is too fast, the Unit is too large (in seconds). This result is returned after rounding (the value may not be 0 if it is changed to milliseconds ). The results of each execution are not necessarily the same. However, from this small experiment, we can see that the index-based table is a little faster than the non-index-based table, but it is not absolute. Whether or not to use the index technology depends on the specific problem. If a table has a large number of queries, you should increase the index. However, indexes will decrease the table update speed accordingly (because indexes must be updated at the same time ).