Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of mysql permissions and indexes for you. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Mysql permissions and indexing

The highest user of mysql is root.

We can create a user in the database with the statement CREATE USER username IDENTIFIED BY 'password', or execute the CREATE USER username statement to create the user, but this user does not have a password and can set the password after logging in; delete the user statement to DROP USER user; change the username statement to RENAME USER old username to new username

Change the password statement to set password=password ('password')

The statement for an advanced user to change the password of another user is SET PASSWORD FOR user = PASSWORD ('password');.

There are several operations about granting permissions

View user permissions statement as show grants for user

Assign the user permission statement to grant permission on. To user, the first number represents the database, and the second number represents the table to be granted permissions

The statement that cancels the user's rights, REVOKE CREATE ON. FROM user; the refreshed statement is FLUSH PRIVILEGES.

The index of mysql allows us to find the data in the database faster. When we program, we use the column of the class conditionally, and we can design the column involved in the query as an index.

The index has a general index, which has no effect on the data of the column, but the search speed of the data is optimized; the value in the column whose unique index is set as the unique index is unique, and the search speed of the data is also optimized. The primary key index is set as the primary key column will automatically add the primary key index, a table can only have one primary key column, this column does not allow null values, generally, the primary key index is created at the same time when the table is built; the full-text index is mainly used to find keywords in the text, rather than directly comparing with the values in the index. The fulltext index is very different from other indexes in that it is more like a search engine than a simple parameter match for a where statement. Fulltext indexes are used with match against operations, rather than normal where statements plus like. It can be used in create table,alter table and create index, but currently only full-text indexes can be created on char and varchar,text columns. It is worth mentioning that when the amount of data is large, put the data into a table without a global index, and then use CREATE index to create a fulltext index, which is much faster than first establishing a fulltext for a table and then writing the data; there is also a combined index, which can combine two columns as a conditional query together, and a single column as a conditional query will not have the effect of an index.

The statement that creates the index is the CREATE index type [not written as a normal index] INDEX index name ON table (column).

The delete index statement is the DROP INDEX index name ON table.

Disadvantages of the index:

1. Although indexing greatly improves query speed, it also slows down the speed of updating tables, such as insert, update, and delete on tables. Because when updating the table, you should not only save the data, but also save the index file.

two。 Create an index file that takes up disk space. In general, this problem is not too serious, but if you create multiple combined indexes on a large table, the index file will grow very fast. Indexes are only one factor in improving efficiency, and if you have tables with a large amount of data, you need to take the time to study how to build the best indexes, or to optimize query statements.

This is the end of this article on "sample analysis of mysql permissions and indexes". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.