How to improve the execution speed of MySQL scripts?

The SQL statement is incorrect.

What about my answer?

1.Try not to include subqueries in where;

For time queries, try not to write as: where to char(dif date,'yyyy-mm-dd')=to char('2007-07-01 ,'yyyy-mm-dd');

2.In the filter conditions, the conditions that can filter out the maximum number of records must be placed at the end of the where clause;

The table written at the end of the from clause (driving table) will be processed first, and if the from clause contains multiple tables, you must select the table with the fewest records as the base table. If you have more than three join queries, you need to select an intersection table as the underlying table, which is the table that is referenced by other tables.

3.Adopt bound variables.

4.Try not to use or in where

5.Replace in with exists, not exist instead of not in;

6.Avoid using calculations on index columns: where sal*12>25000;

7.Use in instead of or: where loc id=10 or loc id=15 or loc id=20

8.Avoid using is null and is not null on index columns;

9.Always use the first column of the index;

10.Replace union with union-all;

11.Avoid changing the type of the index column: select....from emp where empno='123', due to the implicit data type conversion, to char(empno)='123', therefore, the index will not be used, and it will generally appear in dynamic SQL statements that use string patchwork;

12.’!=' will not use the index;

13.Optimize group by;

14.Avoid wildcards with like arguments, like '4ye%' uses indexes, but like '%ye' doesn't.

15.Avoid using difficult formal expressions, such as select * from customer where zipcode like "98 Even if the index is built on the zipcode, in this case it is still sequential scanning. If you change the statement to select * from customer where zipcode>"98000, the index will be used to query when executing the query, which will obviously greatly improve the speed;

16.Complete SQL statements as explicit as possible, and let the database work as little as possible. For example, when you write a select statement, you need to specify the table name of the query field.

Try not to use the select * statement. When organizing SQL statements, try to organize them according to the habits of the database.