数据库应用2023-04-01

index vs fulltext index in mysql

In MySQL, an index is a data structure that improves the speed of data retrieval operations on a table. It works by allowing the database to find and retrieve specific rows more quickly, by reducing the amount of data that needs to be scanned to find the desired rows.

A fulltext index, on the other hand, is a type of index that is designed specifically for full-text searches. It is used to speed up text-based search queries by creating a separate data structure that indexes the words in the text column of a table. When a search query is executed, the fulltext index is used to quickly identify the rows that contain the specified search terms, without having to scan the entire text column.

The main difference between a regular index and a fulltext index is that a regular index is used for exact matches on specific columns, whereas a fulltext index is used for text-based searches that match specific words or phrases within a column. Additionally, a fulltext index is typically much larger than a regular index, as it needs to store information about the position and frequency of each word within the indexed text column.

In summary, if you need to search for specific words or phrases within a text column, a fulltext index is the way to go. However, if you are only searching for exact matches on a specific column, a regular index is sufficient.

alter table add index in mysql

ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (column1, column2, ...);

table_name: The name of the table you want to add an index to.
[UNIQUE|FULLTEXT|SPATIAL]: Optional. Specifies the type of index to create. UNIQUE creates a unique index, FULLTEXT creates a full-text index, and SPATIAL creates a spatial index.
index_name: The name of the index you want to create.
(column1, column2, ...): The name of one or more columns that you want to include in the index.

The correct syntax for adding multiple indexes in MySQL using ALTER TABLE is:

sql

ALTER TABLE table_name 
ADD INDEX index1_name (column1), 
ADD UNIQUE index2_name (column2), 
ADD FULLTEXT index3_name (column3);

Here's an example that adds two indexes:

sql

ALTER TABLE student
ADD INDEX idx_sNo (sNo),
ADD UNIQUE idx_sName (sName);

This will add an index named idx_sNo on the sNo column and a unique index named idx_sName on the sName column of the student table.

drop an index from table in mysql

To drop an index from a table in MySQL, you can use the DROP INDEX statement followed by the name of the index and the name of the table. Here's the syntax:

graphql

DROP INDEX index_name ON table_name;

For example, if you have an index named index_name on a table named my_table, you can drop it using the following command:

graphql

DROP INDEX index_name ON my_table;

Note that dropping an index can have performance implications, so be sure to consider the impact on your queries before doing so.

In MySQL, you can add and drop constraints on a table using the ALTER TABLE statement. Here are the syntaxes for adding and dropping constraints:

To add a constraint:

sql

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_list);

Here, table_name is the name of the table you want to add the constraint to, constraint_name is the name you want to give to the constraint, constraint_type is the type of constraint you want to add (e.g. PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.), and column_list is a comma-separated list of the columns you want to include in the constraint.

For example, to add a primary key constraint on the id column of a table named users:

sql

ALTER TABLE users
ADD CONSTRAINT pk_users PRIMARY KEY (id);

To drop a constraint:

sql

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Here, table_name is the name of the table you want to remove the constraint from, and constraint_name is the name of the constraint you want to drop.

For example, to drop the primary key constraint we added earlier:

sql

ALTER TABLE users
DROP CONSTRAINT pk_users;

add a foreign key in mysql , with delete and update restrict

To add a foreign key in MySQL with delete and update restrict, you can use the following SQL statement:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column) REFERENCES parent_table(parent_column)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

Make sure to replace the child_table, child_column, parent_table, parent_column, and fk_name with your specific table and column names and foreign key name.

The ON DELETE RESTRICT and ON UPDATE RESTRICT options restrict any changes to the parent table's primary key, which is referenced by the child table's foreign key. It means that you cannot delete or update the parent table's primary key value as long as it is being referenced by the child table.

If you don't specify any ON DELETE action, MySQL will allow you to delete rows from the parent table even if they are referenced in the child table. This can lead to data inconsistencies if you accidentally delete a row that is still being used in the child table.

By specifying ON DELETE RESTRICT, you are telling MySQL to prevent deletion of any rows from the parent table that are still being referenced in the child table. Similarly, ON UPDATE RESTRICT prevents you from updating the value of the parent key if it is still being referenced in the child table. This helps to ensure data consistency and prevent accidental deletions or updates that could break referential integrity.