Index and Performance Analysis

1. How can we know the efficiency of SQL Execution ??? 1. set @ profiling1; 2. Execute the statement. 3. showproliles; mysqlshowprofiles; Emptyset (0.00sec) mysqlset @ profiling1; QueryOK, 0 rowsaffected (0.00sec) mysqlselect * fromwtdoctororderbyrand ()

1. How can we know the efficiency of SQL Execution ??? 1. set @ profiling = 1; 2. Execute the statement. 3. show proliles; mysql show profiles; Empty set (0.00 sec) mysql set @ profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql select * from wtdoctor order by rand ()

1. How can we know the efficiency of SQL Execution ???

1. set @ profiling = 1;

2. Execute the statement.

3. show proliles;

Mysql> show profiles;

Empty set (0.00 sec)


Mysql> set @ profiling = 1;
Query OK, 0 rows affected (0.00 sec)


Mysql> select * from wtdoctor order by rand () limit 2 \ g
+ ------------- + --------------- + -------------------------------------------------
+ ------------------- + --------------- + ------------------ + --------
------------ + ----------- +
| WmxDoctorID | wmxDoctorName | wmxDoctorPhoto
| WmxProfessionalID | wmxAcademicID | wmxHospitalID | wmxDepartmentsID | wmxDise
AsesClassID | inputtime |
+ ------------- + --------------- + -------------------------------------------------
+ ------------------- + --------------- + ------------------ + --------
------------ + ----------- +
| 5 | glass |./default/Tpl/image/2014/03/04/139391281120 .jpg
| Technology | doctor | unknown | all vertices
| 0 |
| 3 | location |./default/Tpl/image/2014/02/24/139322651638 .jpg
| Location | d | location |
| 0 |
+ ------------- + --------------- + -------------------------------------------------
+ ------------------- + --------------- + ------------------ + --------
------------ + ----------- +
2 rows in set (0.00 sec)


Mysql> show profiles;
+ ---------- + ------------ + ------------------------------------------------ +
| Query_ID | Duration | Query |
+ ---------- + ------------ + ------------------------------------------------ +
| 1 | 0.00058350 | select * from wtdoctor order by rand () limit 2 |
+ ---------- + ------------ + ------------------------------------------------ +

1 row in set (0.00 sec)

To view the statement execution details:

Mysql> show proliles for query 1;

Ii. mysql execution plan.

The keyword "explain" is put in front of the select statement. mysql explains how it will process the select statement and provides information about how the table is joined and in what order it is joined.

What can we do?

1. When must we add an index to the table to obtain a faster select method for using the index to find records.

2. Whether the optimizer joins the table in an optimal order.

Mysql> explain [extended] select * from t;

When you plan and analyze complex queries, you may obtain multiple execution plans.

For example:

Mysql> explain select * from t;

3. Basic principles for creating and using MYSQL indexes!

1. Design and use indexes reasonably.

2. on the index of the keyword segment, the query speed is nearly 100 times different from that without an index.

3. Poor indexes have the same effect as no indexes.

4. The more indexes, the better, because the cost of maintaining indexes is required.

5. The index of each table should be less than 5, and some indexes and joint indexes should be used reasonably.

6. indexing is not performed on a column with a single result in the result set. For example, if the gender field has only 0 and 1 results, creating an index on this field does not help much.

7. The result set of index creation should be evenly distributed or conform to the normal distribution.