MySQL indexing and optimization queries

from: http://blog.chinaunix.net/uid-29532375-id-4144615.html indexing and Optimizing queriesAn appropriate index can speed up queries and can be divided into four types: primary key, unique index, full-text index, normal index.
PRIMARY Key: Unique and no null value.
CREATE TABLE pk_test (F1 int NOT NULL, PRIMARY Key(F1));
ALTER TABLE customer modify ID int not NULL, Add primary Key(ID);
Normal Index: Allows duplicate values to appear.
CREATE TABLE Tableanme (fieldname1 columntype,fieldname2 ColumnType, Index[IndexName] (FieldName1 [, fieldname2 ...]));
CREATE TABLE TableName Add Index[IndexName] (FieldName1 [fieldname2 ...]);
ALTER TABLE Slaes Add index(value);
Full-Text indexing: Used to index a large table's text field (Char,varchar,text).
Syntax is as-fulltext as a normal index.
UseFull-text index: CREATE TABLE ft2 (F1 varchar (255), Fulltext (F1));
INSERT into FT2 values ("Wating for the Bvarbariands"), (' In the heart of the country '), (' The Master of Petersburg '), (' Writi Ng and Being '), (' Heart of the Beast '), (' Master Master ');
SELECT * from FT2 where match (F1) against (' master '); Match ()-matches the field; against () matches the value.
MySQL will ignore certain words, causing Error in Query: A. More than 50% of the words appearing in the field; b. Words less than three words; C.mysql predefined list, including the. Query statement: SELECT * from FT2 where match (F1) against (' The Master '); is different from the desired result.
Relevance score Query: Select F1 , (Match (F1) against (' master '))from FT2;
New features of Mysql4- Boolean Full-text query: SELECT * from FT2 where match (F1) against (' +master-pet ' In boolean mode); Operator Type +-<> () ~* "
Unique index: Other than the normal index, except that there cannot be duplicate records.
CREATE TABLE Ui_test (F1 int,f2 int,unique (F1));
ALTER TABLE ui_test add unique (F2);
For a domain (varchar,char,blob,text) PartCREATE INDEX: ALTER TABLE customer Add index (surname ( Ten));
Automatically increase domain: Automatically increments the value of a field each time a record is inserted, only for one domain, and the field is indexed.
CREATE TABLE tablename (fieldname int auto_increment, [fieldname2 ...,] primary key (Filedname));
ALTER TABLE TableName Modify FieldName ColumnType auto_increment;
The last_insert_id () function returns the newly inserted auto-increment value.
Select last_insert_id ()from customer limit 1;
This function can cause errors when multiple connections are in progress.
ResetAutomatically increase the value of the counter:
CREATE TABLE tablename (fieldname int auto_increment,[fieldname2 ...,] primary key (Filedname) auto_increment=50);
ALTER TABLE TableName auto_increment=50;
If the reset value is smaller than the existing value, the auto-increment counter increments the count from the largest value in the record, such as the Customer table has 1, 2, 3, 15, 16, 20, and when the auto-increment counter is set to 1 o'clock, the next inserted record starts at 21.
Automatically increases the counter's Out of bounds: Valid value is 127 times, or 2147483647. If this value is exceeded (including negative values), MySQL automatically sets it to the maximum value, which results in a duplicate key value error.
Automatically increase the domain in in multi-column indexesUse of:
CREATE table staff (rank enum (' employee ', ' manager ', ' contractor ') not null,position varchar (+), id int not NULL auto_ Increment,primary key (Rank,id));
INSERT into staff (rank,position) VALUES (' Employee ', ' cleaner '), (' Cotractor ', ' Network Maintenance '), (' manager ', ' Sales Manager ');
When you add some data to each level, you see the familiar auto-add phenomenon:
INSERT into staff (rank,position) VALUES (' Employee ', ' Cleaner1 '), (' Employee ', ' Network Maintenance1 '), (' manager ', ' Sales Manager1 ');
In this scenario, the auto-increment counter cannot be reset.
Delete or change an index: Changes to the index need to be deleted and redefined.
ALTER TABLE TableName Drop PRIMARY Key;
ALTER TABLE Table DROP IndexIndexName;
DROP Index onTableName
Efficient use of indexes: The following discussion is about what the index will bring us?
1.) Get the matching line from the domain WHERE clause: SELECT * FROM Customer where surname> ' C ';
2.) When looking for the max () and Min () values, MySQL simply finds the first and last values in the sorted index.
3.) The returned part is part of the index, and MySQL does not need to query the entire table for data but only the index: SELECT IDfrom customer;
4.) Where to use order by for a domain: SELECT * FROM Customer Order bySurname
5.) can also accelerate the connection of tables: Select First_name,surname,commission from Sales,sales_rep where Sales.sales_rep=sales_rep.employee_numberand code=8;
6.) In the case of a wildcard: SELECT * from Sales_rep where surname Like ' ser% ';
This situation does not work: SELECT * from Sales_rep where surname like '%ser% ';
Select index:
1.) To create an index when a query needs to use an index (such as the domain of a condition in a WHERE clause), do not use a domain that is not used (as the first character is a wildcard) to create an index.
2.) The fewer rows that are created by the index, the better the primary key, and the index of the enumerated type is not useful.
3.) Use a short index (for example, the first 10 characters of a name instead of all).
4.) Do not create too many indexes, although speed up the query, but increase the time to update the added record. Do not create an index if the index is seldom used in a query, but does not have an index that only has a slight effect on speed.
left-most rule: This happens on multiple indexed domains, and MySQL uses them sequentially, starting at the far left of the index list.
ALTER TABLE customer add initial varchar (5);
ALTER TABLE Customer Add index (surname,initial,first_name);
Update customer set initial= ' X ' where id=1;
Update customer set initial= ' C ' where id=2;
Update customer set initial= ' V ' where id=3;
Update customer set initial= ' B ' where id=4;
Update customer set initial= ' n ' where id=20;
Update customer set initial= ' m ' where id=21;
If the three domains are used in the query, the index is maximized: SELECT * from customer where surname= ' Clegg ' and initial= ' x ' and First_name= ' Yvonne ';
Or use most of the index: SELECT * from customer where surname= ' Clegg ' and initial= ' X ';
Or just surname:select * from customer where surname= ' Clegg ';
If break the leftmost rule, the following example does not use the index: SELECT * from customer where initial= ' x ' and First_name= ' Yvonne ';
SELECT * FROM customer where initial= ' X ';
SELECT * FROM customer where first_name= ' Yvonne ';
SELECT * FROM customer where surname= ' Clegg ' and first_name= ' Yvonne ';

using explain-Explains how MySQL uses indexes to process SELECT statements and join tables.
Enter explain select * from Customer; After that, a table appears, with the following lines meaning:
Table-Shows which table the row data belongs to; type-An important column that shows what kind of connection is used, from good to bad in order of const, EQ_REF, ref, range, index, all, detailed below; Possible_keys-the index that can be applied to this table, or null, indicates that no index is available; Key-the actual index used, such as NULL, indicates that no index is used; Key_len-The length of the index, in the case of no loss of accuracy, the shorter the better; ref-Shows which column of the index is used and, if possible, a constant; rows-Returns the number of rows for the requested data; Extra-Additional information on how MySQL resolves the query, as detailed below.
description of the extra line: Distinct-mysql found the row of the domain row union match, no longer search;
Not Exists-mysql optimizes the left join, and once the row matching the left join is found, it is no longer searched;
Range checked for each-did not find the ideal index, once for each row from the preceding table combination;
Record (Index map: #)-Check which index is used and use it to return rows from the table, which is one of the slowest indexes to use;
Using filesort-see this and need to optimize the query, MySQL needs additional steps to find out how to sort the rows returned. He sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.
The Using index-column data is returned from a table that uses only the information in the index and does not read the actual rows, which occurs when all the request columns of the table are the same index;
Using temporary-to see this you need to optimize the query, MySQL needs to create a temporary table to query the storage results, which usually occurs in many different lists when the order by, rather than group by;
Where used-uses a WHERE clause to restrict which rows will match the next table or return to the user. If you do not want to return all the rows used in the table, and the connection type is all or index, this will happen, or there may be a problem with the query.
Description of type: The system-table has only one row, which is a special case of the const connection type; The maximum value of a record in a const-table can match the query (the index can be a primary key or a unique index). Because there is only one row, this value is actually a constant, because MySQL reads this value first, and then treats it as a constant; eq_ref-from the preceding table, the union of each record reads a record from the table. Used when the query uses all of the index primary key or unique index; ref-occurs only if a part that is not a primary key or a unique index is used. For each row of the preceding table, all records are read from the table, and the connection type is heavily dependent on how much the index matches the record-the less the better; range-uses an index to return rows in a range, such as when using > or < find ; index-This connection type makes a full scan of each record in the preceding table (better than all, because the index is generally less than the table data); all-This connection type is a complete scan of each record in the previous table, which is worse and should be avoided as much as possible.
As an example: Create INDEX Sales_rep on sales (SALES_REP); You can compare the changes before and after creating an index
Explain select * from Sales_rep left joins sales on sales.sales_rep = Sales_rep.employee_number;
The results are as follows:

Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
Sales_rep	All	Null	Null	Null	Null	5
Sales	Ref	Sales_rep	Sales_rep	5	Sales_rep.employee_number	2

This result indicates that the Sales_rep table has a bad connection type-all, useless to the index, the number of rows to query for 5;sales connection type is ref, the available index is sales_rep, the actual use of the Sales_rep index, the length of the index is 5, The corresponding column is Employee_number, the number of rows to query is 2, so this query has a total of 5x2 queries to the table.
View index Information : Show index from TableName;
description of the column: table-the name of the table being viewed; Non_unique-1 or 1.0 indicates that the index cannot contain duplicate values (primary key and unique index), 1 means yes, key_name-index name, and the order of the columns in the seq_in_index-index, starting with 1 ; column_name-column name; collation-a or null,a indicates that the index is ordered ascending, null is not sorted, and the number of unique values in the cardinality-index; sub_part-if the entire column is indexed, the value is null. Otherwise, the size of the index is expressed as a character, packed-is packaged, and null-if the column can contain null yes;comment-various annotations.

MySQL indexing and optimization queries