MySQL co-development daily

The InnoDB engine differs from the MyISAM engine.

MyISAM	InnoDB
	row-level lock
non-object security	things security
full-text indexing	full-text indexing not supported
OLAP Online Analytical Processing	OLTP online things processing

SQL optimization and fair use index

1 No in sentence query optimization

2 pattern matching like '%xxx% ' optimization

3 Limit Paging optimization

4 COUNT (*) Statistics acceleration

One count (secondary index) faster than COUNT (*)

Two count (distict) optimizations

5 or condition optimization

6 using the On duplicate key update phrase

7 Not to sort of

8 unnecessary nested SELECT queries

9 Unnecessary table self-connection

10 replacing the HAVING clause with a WHERE clause

Fair use Index

1 single-row index and Federated index optimization

2 field use function, will not be used to index

3 Fatal full table scan without quotes, index not available

4 when the number of fetched exceeds 20% of the data in the table, the optimizer does not use the index, but the full table scan

5 consider not indexing for some columns

6 Order By,group by optimization

New features supported by MySQL5.6

1 Explain update

2 InnoDB engine supports full-text indexing

3 support for Multi-range Read index optimization

4 optimized the InnoDB merge merge index

5 support InnoDB Condition Pushdown Index optimization

This article from the "Clear Sky" blog, declined reprint!

MySQL co-development daily