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.UNIQUEcreates a unique index,FULLTEXTcreates a full-text index, andSPATIALcreates 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:
sqlALTER 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:
sqlALTER 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.
标签：index,fulltext,01,name,04,column,text,2023,table From： https://www.cnblogs.com/chucklu/p/17278612.html