Detailed explanation of mysql permissions and indexes

mysql permissions and indexes

The highest user of mysql is root.

We can create a user in the database with the statement CREATE USER username IDENTIFIED BY 'password'. We can also execute the CREATE USER username statement to create a user, but this user does not have a password. We can set the password after the user logs in. The statement to delete a user is DROP USER user. The statement to change the username is RENAME USER old username to new username.

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

The statement for advanced users to modify other users' passwords is SET PASSWORD FOR user=PASSWORD('password'); .

There are several operations for granting permissions:

To view user permissions, use the show grants for user statement.

The statement for granting user permissions is grant permission on . to user. The first number represents the database, and the second number represents the table to which permissions are to be granted.

The statement to revoke user privileges is REVOKE CREATE ON . FROM user; the statement to refresh privileges is FLUSH PRIVILEGES.

MySQL indexes allow us to find data in the database faster. When we are programming, we can use the column in the condition to design the columns involved in the query as indexes.

There are two types of indexes: ordinary index. Setting it as an ordinary index has no effect on the data in the column, but it optimizes the data search speed; the values ​​in the column set as a unique index are unique, which also optimizes the data search speed; the primary key index is set as the primary key column and the primary key index is automatically added. A table can only have one primary key column, and this column does not allow null values. Generally, the primary key index is created at the same time as the table is created; 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. It is more like a search engine rather than a simple parameter match of a where statement. The fulltext index is used in conjunction with the match against operation, rather than the general where statement plus like. It can be used in create table, alter table, and create index, but currently only char, varchar, and text columns can be used to create full-text indexes. It is worth mentioning that when the amount of data is large, it is much faster to put the data into a table without a global index and then use CREATE index to create a fulltext index than to first create a fulltext index for a table and then write the data. There is also a composite index, which can combine two columns as conditions for querying together. Using a single column as a condition for querying will not have the effect of an index.

The statement to create an index is CREATE index type [not written as ordinary index] INDEX index name ON table (column).

The statement to delete an index is DROP INDEX index name ON table.

Disadvantages of indexing:

1. Although indexes greatly increase query speed, they also reduce the speed of updating tables, such as inserting, updating, and deleting tables. Because when updating a table, not only the data but also the index file must be saved.

2. Creating an index will take up disk space for index files. Generally this problem is not serious, but if you create multiple combined indexes on a large table, the index file will grow quickly. Indexing is only one factor in improving efficiency. If you have a table with a large amount of data, you need to spend time researching and establishing the best index or optimizing query statements.

The above is all the knowledge about MySQL permissions and indexes. Thank you for your support of 123WORDPRESS.COM.