Index advantages: 1. Generally, the rows that match the conditions given by the WHERE clause are the rows that match other data tables in the join operation. 2. For Column 3 that is queried using the mix () and max () functions, column 4 of Order by and group by is often used, and the index can speed up the query, no need to scan the entire table index disadvantage 1. Although the index accelerates the query speed, it will reduce write operations, such as insert, modify, and delete data. 2. The index occupies disk space, the more indexes, the larger the space occupied. For MyISAM tables, a large number of indexes on a data table may make the index file faster than the data file to reach its size limit. For InnoDB, all InnoDB Data Tables share the same bucket. Adding an index will reduce the storage space used by the tablespace. The matching speed of hash indexes for "=" and "<=>" operations is extremely fast, but the range search and comparative search operations are relatively slow. B-tree index for <, <=,>, >=, = <> ,! = The query efficiency of the between operation is high. For a string that starts with a pure string instead of a wildcard, B-tree can use the like operator for pattern matching. Slow Query Mysqldumpsqlow 1. Primary Key Index 2. Unique Index 3. Regular Indexes 4. Full-text index Create an index. The default value is ASC sorting. Create index index_name on tbl_name (column ASC {default} | DESC) If the relevant table has been created using the memory engine, the hash index is used. Create index index_name using btree | hash on tbl_name (column ASC) Create a composite index Create index index_name on tbl_name (column1, column2) Create Table carts (cartid int not null, userid, bookid, number, primary key (cartid), key index_name (userid, bookid )); Unique Index Create unique index index_name on tbl_name (column) Optional: Unique | Fulltext | spatialcreate table Cate (cateid int not null auto_increment primary key, catename varchar not null unique catemenu varchar not null Fulltext ); use the alert statement to add an index alert table tbl_name add index index_name using btree (column) add an index create table tbl_name (column1 int not null Primary Key , Column2 int not null, column3 smallint not null, Index index_name (Column) Unique indxe index_name using hash (Column2, column3 )) Full-text index Full-text search is executed together with the match () function.
Mysql> Create Table articles (
Id int unsigned auto_increment not null primary key,
Title varchar (200 ),
Body text,
Fulltext (title, body)
); 1. Any word that is too short will be ignored. The default minimum length of words that can be found in full-text search is 4 characters. 2, Words in the stopword are ignored. A disabled word is a word that is too common and is considered non-semantic like "the" or "some. A built-in stop word exists, but it can be rewritten through the user-defined list. 3, The word appears in the row of 50%. It is included in the stopword. Select book_name, price from books where match (column) against ("string"); drop index Index_name On tbl_name View A view is a virtual table with the same behavior as a data table but does not contain real data. Create view syntax Create view <view Name> [<column list>] As <Table expression> [with [cassaded | Local] Check option] Create View Create view view_name as select column1, column2 from tbl_name Where xxx> xx Specify columns when creating a view Create view view1 (va1, va2, va3) as select va1, va2, va3 from TBL where va1 <num; You can use table join when creating an index. Creaet view VST as select ID, name, date, score category from grade_event inner join score inner join Stu on grade_event.id = score. event_id and score. ID = Stu. IDWhen creating an index, you can place the mathematical operation in a column in the view. Create view view_demo select name, birth, death timstampdiff (year, birth, death) as age from TBL
