Detailed description of mysql permissions and indexes, detailed description of mysql permission Indexes

Detailed description of mysql permissions and indexes, detailed description of mysql permission Indexes

Mysql permissions and Indexes

The highest user of mysql is root,

You can CREATE a USER in the database. The statement is "create user username identified by 'Password'. You can also execute the" create user username "statement to CREATE a USER. However, this USER does not have a password, you can set the password after logging on to the USER. The delete USER statement is drop user. The statement for changing the USER name is RENAME old USER name to new USER name;

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

The statement FOR advanced users to change their passwords is set password for user = PASSWORD ('Password ');.

You can perform the following operations to grant permissions,

View the user permission statement as show grants for the user;

Grant user permission statement grant permission on.To user, firstNumber indicates the database, the secondNumber indicates the table to be granted permissions;

Statement for canceling User Permissions REVOKE CREATE ON.FROM user; the refresh statement is flush privileges.

Mysql indexes allow us to search for data in the database at a faster speed. When programming, we use the column class on the condition, you can design the columns involved in the query as indexes.

An index has a common index. Setting an index as a common index does not affect the data in a column, but the data search speed is optimized. The values in a column with a unique index are unique, the data search speed is also optimized. If the primary key index is set as the primary key column, the primary key index is automatically added. A table can only have one primary key column, and null values are not allowed for this column, generally, a primary key index is created when a table is created. The full-text index is mainly used to search for keywords in the text, rather than directly comparing them with the values in the index. The fulltext index is much different from other indexes. It is more like a search engine than a simple where statement Parameter Match. Fulltext indexes are used in combination 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 the char, varchar, and text columns can create full-text indexes. It is worth mentioning that when the amount of data is large, the data is now put into a table without a global index, and then CREATE a fulltext index with CREATE index, it is much faster to create fulltext for a table and then write data to the table. There is also a composite index, which can combine two columns for query together as conditions, A single column as a condition query does not have an index effect.

The statement for creating an INDEX is the create index type. [do not write it as a normal INDEX] INDEX name ON table (column ).

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

Disadvantages of indexing:

1. Although the index greatly improves the query speed, it also reduces the speed of updating the table, such as performing insert, update, and delete operations on the table. Because when updating a table, you must not only save data, but also save the index file.

2. index files that occupy disk space when an index is created. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will grow rapidly. Indexing is only a factor to improve efficiency. If there is a large amount of data in a table, it takes time to study the best indexes or Optimize Query statements.

The above is all about mysql permissions and indexes. Thank you for your support for the help house.