Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Fulltext Search

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Full-Text Search

      The MySQL Full-Text Search allows us to search for a text-based data, stored in the database. Before performing the full-text search in a column(s) of table, we must create a full-text index on those columns.
      The FTS (full-text search) provides the capability to match the searched string value through large text content such as blogs, articles, etc.

      MySQL Full-Text Search

      To perform a Full-Text Search on a MySQL table, we use MATCH() and AGAINST() functions in a WHERE clause of an SQL SELECT statement.
      Stop words are words that are commonly used (such as 'on', 'the', or, 'it') in sentences and will be ignored during the searching process.
      The basic syntax to perform a full-text search on a MySQL is as follows −
      SELECT column_name(s) FROM table_name 
      WHERE MATCH(col1, col2, ...) 
      AGAINST(expression [search_modifier])
      
      Here,
      • MATCH() function contains one or more columns separated by commas to be searched.
      • AGAINST() function contains a search string to use for the full-text search.

      Key Points of MySQL Full-Text Search

      Following are some key points about the full-text search in MySQL −
      • Either InnoDB or MyISAM tables use the full-text indexes. The minimum length of the word for full-text searches is three characters for InnoDB tables and four characters for MyISAM tables.
      • Full-Text indexes can be created on text-based columns (CHAR, VARCHAR or TEXT columns).
      • A FULLTEXT index can be defined while creating the table using CREATE TABLE statement or can be defined later using the ALTER TABLE or CREATE INDEX statements.
      • Without FULLTEXT index, it is faster to load large data sets into a table than to load data into a table which has an existing FULLTEXT index. Therefore it is recommended to create the index after loading data.

      Types of Full-Text Searches

      There are three types of full-text searches. The same is described below:
      • Natural Language Full-Text Searches: This allows the user to enter the search query in a natural human language without any special characters or operators. The search engine will examine the query entered by the user and returns the relevant results based on the user's intent.
      • Boolean Full-Text Searches: This allows us to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators such as +, -, >, <, (), ~, *, "".
      • Query Expansion Searches: This expands the user's query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion.

      Creating MySQL FULLTEXT Index

      In MySQL, we can define a full-text index on particular column while creating a new table or on an existing table. This can be done in three ways:
      • Using the FULLTEXT Keyword
      • Using the ALTER TABLE Statement
      • Using the CREATE INDEX Statement

      Using the FULLTEXT Keyword

      To define full-text index on a column while creating a new table, we use the FULLTEXT keyword on that column within the CREATE TABLE query. Following is the syntax −
      CREATE TABLE table_name(  
         column1 data_type,  
         column2 data_type,
         ...,
         FULLTEXT (column1, column2, ...)  
      );
      
      Example
      Let us create first a table named FILMS and define the full-text index on NAME and DIRECTOR columns, using the following query −
      CREATE TABLE FILMS (
         ID int auto_increment not null primary key,
         NAME varchar(50),
         DIRECTOR TEXT,
         FULLTEXT (NAME, DIRECTOR)
      );
      
      Now, let us insert values into this table using the following query −
      INSERT INTO FILMS (NAME, DIRECTOR) VALUES 
      ('RRR', 'Directed by Rajamouli'),
      ('Bahubali', 'Directed by Rajamouli'),
      ('Avatar', 'Directed by James cameron'),
      ('Robot', 'Directed by Shankar');
      
      The table will be created as −
      ID
      NAME
      DIRECTOR
      1
      RRR
      Directed by Rajamouli
      2
      Bahubali
      Directed by Rajamouli
      3
      Avatar
      Directed by James Cameron
      4
      Robot
      Directed by Shankar
      Here, we are fetching all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ‘Rajamouli’ using the MATCH and AGAINST functions as shown below −
      SELECT * FROM FILMS 
      WHERE MATCH (NAME, DIRECTOR) 
      AGAINST ('Rajamouli');
      
      Output
      As we can see in the output below, the full-text search has been performed against a string ‘Rajamouli’ and it returned the rows which contains this string.
      ID
      NAME
      DIRECTOR
      1
      RRR
      Directed by Rajamouli
      2
      Bahubali
      Directed by Rajamouli

      Using the ALTER TABLE Statement

      In MySQL, we can create full-text index on particular columns of an existing table using the ALTER TABLE statement. Following is the syntax −
      ALTER TABLE table_name    
      ADD FULLTEXT (column1, column2,...)
      
      Example
      In this example, we are defining a full-text index named FULLTEXT on NAME and DIRECTOR columns of the previously created FILMS table −
      ALTER TABLE FILMS ADD FULLTEXT (NAME, DIRECTOR);
      
      Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string 'Shankar'.
      SELECT * FROM FILMS 
      WHERE MATCH (NAME, DIRECTOR) 
      AGAINST ('Shankar');
      
      Output
      Following is the output −
      ID
      NAME
      DIRECTOR
      4
      Robot
      Directed by Shankar

      Using the CREATE INDEX Statement

      In MySQL, we can also create a full-text index for an existing table using the CREATE INDEX statement. Following is the syntax −
      CREATE FULLTEXT INDEX index_name  
      ON table_name (index_column1, index_column2,...)
      
      Example
      We are creating a full-text index with the name INDEX_FULLTEXT on the NAME and DIRECTOR column of the FILMS table −
      CREATE FULLTEXT INDEX INDEX_FULLTEXT ON FILMS (NAME, DIRECTOR);
      
      Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string value as shown in the below query −
      SELECT * FROM FILMS 
      WHERE MATCH(NAME, DIRECTOR) 
      AGAINST ('James Cameron');
      
      Output
      Following is the output −
      ID
      NAME
      DIRECTOR
      3
      Avatar
      Directed by James Cameron

      Dropping MySQL FULLTEXT index

      In MySQL, we can remove or drop a full-text index from a table using the ALTER TABLE DROP INDEX statement.
      Syntax
      Following is the syntax −
      ALTER TABLE table_name DROP INDEX index_name; 
      
      Example
      In the following query, we will delete the previously created full-text index −
      ALTER TABLE FILMS DROP INDEX INDEX_FULLTEXT;
      
      Verification
      Let us verify whether the index is dropped or not by executing the below query −
      SELECT * FROM FILMS 
      WHERE MATCH(NAME, DIRECTOR) 
      AGAINST ('James Cameron');
      
      As we can see in the output, the full-text index is removed on the NAME and DIRECTOR columns.
      ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
      

      Full-Text Search Using Client Program

      In addition to performing the full-text search using MySQL Query, we can also do so using the client program.

      Syntax

      PHPNodeJSJavaPython
      To perform the Fulltext Search on a MySQL database through a PHP program, we need to execute the CREATE TABLE statement using the mysqli function query() as follows −
      $sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
      $mysqli->query($sql);
      

      Example

      Following are the programs −
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');    
      //creating a table films that stores fulltext.
      $sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
      $result = $mysqli->query($sql);
      if ($result) {
          printf("Table created successfully...!\n");
      }
      //insert data
      $q = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES ('RRR', 'The film RRR is directed by Rajamouli'), ('Bahubali', 'The film Bahubali is directed by Rajamouli'),
            ('Avatar', 'The film Avatar is directed by James cameron'), ('Robot', 'The film Robot is directed by Shankar')";
      if ($res = $mysqli->query($q)) {
          printf("Data inserted successfully...!\n");
      }
      //now display the table records    
      $s = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli')";
      if ($r = $mysqli->query($s)) {
          printf("Table Records: \n");
          while ($row = $r->fetch_assoc()) {
              printf(" ID: %d, Name: %s, Director: %s", $row["ID"], $row["NAME"], $row["DIRECTOR"]);
              printf("\n");
          }
      } else {
          printf('Failed');
      }
      $mysqli->close();
      

      Output

      The output obtained is as shown below −
      Table created successfully...!
      Data inserted successfully...!
      Table Records:
       ID: 1, Name: RRR, Director: The film RRR is directed by Rajamouli
       ID: 2, Name: Bahubali, Director: The film Bahubali is directed by Rajamouli