Prepared Statement
- better performance: db parse/compile/optimize and store the optimized query plan
- prevent SQL injection attacks
Normal Forms
1NF - all columns contain atomic values only
2NF - every non key attribute is fully dependent on the primary key
3NF - every non key attribute is non-transitively dependent on the primary key
Unique key field allows one value as NULL value
- Primary Key creates a clustered index
- Unique key creates unclustered index
Joins
Right Join - Return all rows from the right table, even if there are no matches in the left table
Left Join - Return all rows from the left table, even if there are no matches in the right table
Full Join - Return rows when there is a match in one of the tables
Self-join - used to join a table to itself. Aliases should be used for the same table comparison.
Cross Join - return all records where each row from the first table is combined with each row from the second table.
Cluster vs Non cluster Index
- There can be only one clustered index per table
- A clustered index defines how records are physically stored
- cluster Index are faster
- better performance: db parse/compile/optimize and store the optimized query plan
- prevent SQL injection attacks
Normal Forms
1NF - all columns contain atomic values only
2NF - every non key attribute is fully dependent on the primary key
3NF - every non key attribute is non-transitively dependent on the primary key
Unique key field allows one value as NULL value
- Primary Key creates a clustered index
- Unique key creates unclustered index
Joins
Right Join - Return all rows from the right table, even if there are no matches in the left table
Left Join - Return all rows from the left table, even if there are no matches in the right table
Full Join - Return rows when there is a match in one of the tables
Self-join - used to join a table to itself. Aliases should be used for the same table comparison.
Cross Join - return all records where each row from the first table is combined with each row from the second table.
Cluster vs Non cluster Index
- There can be only one clustered index per table
- A clustered index defines how records are physically stored
- cluster Index are faster
Creating and Using Indexes
The primary optimization technique is to create good indexes and using it effectively in your SQL statement.
Good indexes are important for all SQL operations, not only query, but also update, delete operations.
Principles to create indexes
1. If columns are often used in comparison, consider to create index.
2. Avoid over-indexing
If you never refer to a column in comparison, don't index it, as index has overhead, table updates need to update table's indexes, unnecessary indexing would slow down table updates.
3. If a column has just few distinct values, don't index it, for example: gender column.
4. Declare an indexed column NOT NULL if possible. An index without
NULL can be processed more simply and thus faster.
Indexing Column Prefixes
In some cases, it's sufficient to index just prefix column values rather than complete values; this can improve performance as short index values can be processed more quickly than long ones.
CREATE TABLE t(name CHAR(255),INDEX (name(15)));
Leftmost Index Prefixes
A leftmost prefix of a composite index consists of one or more of the initial columns of the index. MySQL's capability to use leftmost index prefixes enables you to avoid creating unnecessary indexes.
SQL optimization
1. Optimizing Queries by Limiting Output
A simple but effective technique is to reduce the amount of output a query produces.
Select only columns and rows you need, don’t use SELECT * if possible.
Use TOP/limit keyword or the SET ROWCOUNT statement to select only first n rows.
2. Use views and stored procedures instead of heavy-duty queries.
3. Using Indexes Effectively
If the columns are always used in SQL comparison, consider to index them.
Don't refer to an indexed column within an expression that must be evaluated for every row in the table.
SELECT * FROM t WHERE YEAR(d) >= 1994; // inefficient
SELECT * FROM t WHERE d >= '1994-01-01';
When comparing an indexed column to a value, use a value that has the same data type as the column.
For the string value, MySQL must perform a string-to-number conversion, which might cause an index on the id column not to be used.
WHERE id = '18' // id int type, ineffeicent
Use prefix pattern-matching with LIKE operator if possible.
WHERE name LIKE 'de%'
4. Optimizing Updates
Optimization can be also used for update operations.
Use multiple-row INSERT statements instead of multiple single-row INSERT statements.
INSERT INTO t (id, name) VALUES(1,'Bea'),(2,'Belle'),(3,'Bernice');
Group multiple update commands in within a transaction rather than by executing them with auto-commit mode enabled.
Using a transaction allows to flush the changes at commit time.
Using Scheduling Modifiers - MySQL
For an application that uses MyISAM tables, you can change the priority of statements that retrieve or modify data.
Normally, the server will give updates to the table priority over retrievals.
If the application places high importance on having the summary process execute as quickly as possible, it can use scheduling modifiers to alter the usual query priorities.
SELECT HIGH_PRIORITY, LOW_PRIORIT, DELAYED.
Try to avoid using the DISTINCT clause, whenever possible.
Use EXPLAIN to check Query Execution Plan in MySQL and optimize Queries
Resources:
