SQL optimization

Top 10 SQL Query Optimization Tips to Improve Database Performance

十条最优的提升数据库性能的查询语句优化技巧

SQL CREATE INDEX Statement

首先来到创建索引

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

CREATE INDEX Syntax [ˈsɪnˌtæks]

创建语法

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

不同数据库索引略有不同

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

Mysql:

ALTER TABLE table_name
DROP INDEX index_name;

SQL Query optimization is a process of writing thoughtful SQL queries to improve database performance. During development, the amount of data accessed and tested is less. Hence, developers get a quick response to the queries they write. But the problem starts when the project goes live and enormous data starts flooding the database. Such instances slow down SQL queries response drastically and create performance issues.

When working with large-scale data, even the most minor change can have a dramatic impact on performance.

SQL performance tuning can be an incredibly difficult task. Even a minor change can have a dramatic impact on performance. Here are the 10 most effective ways to optimize your SQL queries.

Indexing: Ensure proper indexing for quick access to the database.An index is a data structure that improves the speed of data retrieval operations on a database table. A unique index creates separate data columns without overlapping each other. Proper indexing ensures quicker access to the database, i.e. you’ll be able to select or sort rows faster. The following diagram explains the basics of indexing while structuring tables.
aggregate [ˈæɡrɪɡət]
- n. 总数，合计；骨料，混凝料
- adj. 总计的，合计的；（种群）聚生的
- v. 集合，聚集；总计达到；合计
Select query: Specify the columns in SELECT query instead of SELECT* to avoid extra fetching load on the database.Specify the columns in the SELECT clause instead of using SELECT *. The unnecessary columns place extra load on the database, which slows down not just the single SQL, but the whole system.
Running queries: Loops in query structure slows the sequence. Thus, avoid them.Coding SQL queries in loops slows down the entire sequence. Instead of writing a query that runs in a loop, you can use bulk insert and update depending on the situation. Suppose there are 1000 records. Here, the query will execute 1000 times. Inefficient:
```
for ($i = 0; $i < 10; $i++) {  
  $query = “INSERT INTO TBL (A,B,C) VALUES . . . .”;  
  $mysqli->query($query);  
  printf (“New Record has id %d.\ “, $mysqli->insert_id);
}
```
Efficient
```
INSERT INTO TBL (A,B,C) VALUES (1,2,3), (4,5,6). . . .
```
Matching records:Use EXITS() for matching if the record exists.Normally, developers use EXITS() or COUNT() queries for matching a record entry. However, EXIT() is more efficient as it will exit as soon as finding a matching record; whereas, COUNT() will scan the entire table even if the record is found in the first row.Inefficient:
```
IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’) > 0 PRINT ‘YES’
```
Efficient:
```
IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’)
PRINT ‘YES’
```
Subqueries: Avoid correlated sub queries as it searches row by row, impacting the speed of SQL query processing.A correlated subquery depends on the parent or outer query. Since it executes row by row, it decreases the overall speed of the process.Inefficient:
```
SELECT c.Name, c.City,(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c
```
Here, the problem is — the inner query is run for each row returned by the outer query. Going over the “company” table again and again for every row processed by the outer query creates process overhead. Instead, for SQL query optimization, use JOIN to solve such problems.Efficient:
```
SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co   ON c.CompanyID = co.CompanyID
```
Wildcards: Use wildcards (e.g. %xx%) wisely as they search the entire database for matching results.Wildcard characters can be either used as a prefix or a suffix. Using leading wildcard (%) in combination with an ending wildcard will search all records for a match anywhere within the selected field.Inefficient:
```
Select name from employees where name like ‘%avi%’
```
This query will pull the expected results of Avishek, Avinash, Avik and so on . However, it will also pull unexpected results, such as David, Xavier, Davin. Efficient
```
Select name from employees where name like ‘avi%’.
```
This query will pull only the expected results of Avishek, Avinash, Avik and so on.
Operators: Avoid using function at RHS(right-hand side ) of the operator.Often developers use functions or methods with their SQL queries. Inefficient
```
Select * from Customer where YEAR(AccountCreatedOn) == 2005 and  MONTH(AccountCreatedOn) = 6
```
Note that even though AccountCreatedOn has an index, the above query changes the WHERE clause in such a way that this index cannot be used anymore.Efficient:
```
Select * From Customer Where AccountCreatedOn between ‘6/1/2005’ and ‘6/30/2005’
```
Fetching data: Always fetch limited data.Lesser the data retrieved, the faster the query will run. Rather than applying too many filters on the client-side, filter the data as much as possible at the server. This limits the data being sent on the wire and you’ll be able to see the results much faster.
Loading: Use a temporary table to handle bulk data.If you want to insert thousands of rows in an online system, use a temporary table to load data. Ensure that this temporary table does not have any index. Since moving data from one table to another is much faster than loading them from an external source; you can now drop indexes on your primary table, move data from temporary to the final table, and finally recreate the indexes.
Selecting Rows: Use the clause WHERE instead of HAVING for primary filters.
HAVING clause filters the rows after all the rows are selected. It is just like a filter. Do not use the HAVING clause for any other purposes.

In the SQL Order of Operations, HAVING statements are calculated after WHERE statements. Therefore, executing the WHERE query is faster.