First SQL optimization, it is a big topic, I can't say that to a certain table of a field index is ok, just the innodb storage engine for the way he is B + tree
What is B + tree, his principle is binary tree (it may not seem like?) If this field is too much repetition data which binary tree is indexed in parallel manner makes little sense to the
Also waste of disk space, we should base the actual business situation to analyze, SQL optimization which now how to say, we say that the SQL optimization is really have to know the mysql
Architecture, is to know how an SQL run we can better know how to optimize a SQL!

When a SQL execution first call
SQL interface: 1: accept user SQL commands, and returns the user to query results,
For example, select the from is called SQL Interface

2: whether to open the query cache: if the mysql open the query cache if data accuracy, mysql will retrieve the user permissions, if there is a mysql not into parse SQL operations such as
Returns the data directly, or to the next stage
Optimization point 1:
MySQL will cache in a reference list, (this is not a table, similar to a HashMap data structure), by a hash index, the hash value through the query itself,
Current to query the database, the client protocol version number and other information that may influence the results calculated, so the two queries in any different in characters (Spaces, comments), will be
To cache hit
If a query contains any user-defined functions, stored function, user variables, temporary tables, system tables in MySQL database, the query results will not be cached eg: NOW ()
Query cache write operation faults:
MySQL query cache system tracks the SQL query involved in each table, when involving the query table structure and data changes, which related to this list all cached data
All failure, that is in the cache table think write operation, MySQL must be set corresponding to the failure, if the query cache is large or many fragments, the operating system can bring a lot of consumption,
Even lead to system zombie
Query cache query operation faults:
A: any query statements must be checked before start, even if it never hit of the SQL statement cache
B: if the query result can be cached, then the execution is completed, will result in the cache, also can bring extra system consumption

Query cache can improve system performance, cache and the failure will bring additional systems consumption, especially writing intensive applications (CPU), only when the cache of savings is greater than its own consumption of resources,
Will bring system performance improvement, can try to open the query cache and do some optimization on database design

A: use multiple small table instead of A big table, be careful not to overly design
B: bulk insert instead of recycling a single insert
C: reasonable control the size of the cache space, generally the size set to dozens more appropriate (specific question?)
D: can be controlled through SQL_CACHE and SQL_NO_CACHE whether you need a query cache
SQL_NO_CACHE is forbidden cache the query results, but it doesn't mean the cache are not as a result is returned to the query, the cache before the results after can query to
Method of use:
The SELECT SQL_CACHE COUNT (*) FROM A; COUNT (*) is to see how many,,
Can be specified in the SELECT statement query cache option, for those who must be in real time from the query to get the data in the table, or for those queries executed only once a day,
Can specify the query cache, not use SQL_NO_CACHE option, for those changes don't frequent table, query operation is fixed, the query operation can be cached
Every time, so that the execution is not the actual access to the table and execute queries, just get the result from the cache, can effectively improve the performance of the query, using SQL_CACHE options

For some of the query cache operation
FLUSH the QUERY CACHE: clean up the QUERY CACHE memory fragments
RESET the QUERY CACHE: remove all the QUERY from the QUERY CACHE
FLUSH TABLES: close all open TABLES, at the same time, this operation will clear the contents of the query cache

3: parser: SQL is passed to the parser will be parser validation and parsing (parser is made up of Lex and YACC to realize, is a long script)
Main functions: 1 & gt; The SQL statements into data structures, and the structure to the subsequent steps, after transmission and processing of SQL statements is based on the structure of
: 2 & gt; If the SQL statement encounters an error in the decomposition into a data structure, which means that the SQL is not reasonable
4: the query optimizer: SQL statements can use the query optimizer to optimize a query, he is using a "select - projection - join" query strategy,

With an example can understand: select id, name from the user where name=Tom;
The select queries according to the where clause to select first, rather than the first query table all out later on filter name
Uid and the name of the first select queries based on attribute projection, not remove all attributes later filtering
The two linked to generate the final query results query condition
5: the execution plan - query execution engine - call API interface query - (InnoDB, DBD... Database) -