MySQL indexing and optimization queries

an 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 index : used to index a large table's text field (Char,varchar,text). Syntax is as-fulltext as a normal index. use full-text indexing: 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 '), (' Writing 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 some words, resulting in query error : A. 50% or more fields appear in the word b. Words less than three words; c.mysql a 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;
MYSQL4 new Feature- 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 intauto_increment, [fieldname2 ...,] primary key (Filedname));
ALTER TABLE TableName Modify FieldName ColumnTypeauto_increment;
The last_insert_id () function returns the newly inserted auto-increment value.
Selectlast_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 TableNameauto_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'sOut 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 inin 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 TableNameDrop PRIMARY Key;
ALTER TABLE TableDROP 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 wheresurname> ' 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: SELECTIDfrom customer;
4.) Where to use order by for a domain: SELECT * FROM CustomerOrder bySurname
5.) can also accelerate the connection of tables: Select First_name,surname,commission from Sales,sales_rep whereSales.sales_rep=sales_rep.employee_numberand code=8;
6.) In the case of a wildcard: SELECT * from Sales_rep where surnameLike ' 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 ';
Ifbreak 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 was used, from good to bad in order of const, EQ_REF, ref, range, index, all, as detailed below; Possible_keys-the index that can be applied in this table, or null, indicates that no index is available; Key-the actual index used, such as NULL, indicates that the index is not 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 queries is detailed below.

extra Line Description : Distinct-mysql found the row of the domain row union match, no longer searched,
not Exists-mysql optimized the left join, once found matching left Join line, no longer search;
range checked for each-did not find the ideal index, one time for each row from the preceding table;
record (Index Map: #)-Check which index is used and use it to return rows from the table, which is the slowest use of the index;
using filesort-see this and need to refine the query, MySQL needs additional steps to find out how to sort the returned rows. 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.
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-see this needs to optimize the query, MySQL needs to create a temporary table to query the storage results, which usually occurs in many different lists to 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 this 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 the value first, and then treats it as a constant;eq_ref-from the previous table, the Union for 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 relies heavily on how much of the index match records-the less the better;range-uses an index to return rows in a range, such as when using > or < lookups;index-This connection type makes a full scan of each record in the preceding table (better than all, since the index is generally less than the table data);all-This connection type is more than the previous table each record in the joint full scan, this is worse, should try to avoid.
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