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
      Query Expansion 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.

      Query Expansion Full-Text Search

      In relational databases like MySQL, Full-text search is a technique used to retrieve result-sets that might not perfectly match the search keyword. This type of search is useful in cases where the keywords used for searching do not match the results a user expects. So, this searching technique is designed to focus on increasing search relevance in order to reduce the accuracy gap between search queries and search results. Thus, search results are displayed in the order of highest to the lowest relevancy to the search keyword.
      There are three types of search modes used with Full-text search −
      • Natural Language Mode
      • Query Expansion Mode
      • Boolean Mode

      Query Expansion Full-Text Search

      Search is always done by the user with the limited knowledge they possess. Thus, there are cases when the search keywords are way too short to conduct a proper search. This is where Blind Expansion Search technique comes into picture.
      Blind Expansion Search, also known as Automatic Relevance Feedback, is used to widen the search results based on additional keywords that are closely related to the original keywords. It is enabled using the 'WITH QUERY EXPANSION' search phrase.
      The search is performed twice in this cases by following the steps given below −
      Step 1 − All the rows that match the given search keyword are searched first.
      Step 2 − These obtained rows are then checked for relevant words to the original keyword in them.
      Step 3 − Finally, the rows are searched again based on these relevant words instead of the original keywords specified by the users.
      To perform the query expansion full-text search on a database table, the WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION search modifiers must be specified in the AGAINST() function.

      Example

      Let us understand how to perform Query Expansion Full-text Search on a database table in the following example.
      For that, we will first create a table named DBMS_TUTORIALS containing the title and description of an article. The FULLTEXT index is applied on text columns TUTORIAL_TITLE and DESCRIPTIONS as shown below −
      CREATE TABLE DBMS_TUTORIALS(
         TUTORIAL_TITLE VARCHAR(200), 
         DESCRIPTIONS TEXT, 
         FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS)
      );
      
      Now, let us insert details about tutorials, like their titles and descriptions, into this table using the following queries −
      INSERT INTO DBMS_TUTORIALS VALUES 
      ('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored'),
      ('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored'),
      ('MySQL Security', 'MySQL Database can store sensitive data, so security is required'),
      ('MySQL vs MariaDB', 'Comparing two databases...'),
      ('JDBC Tutorial', 'In this Java-based database connectivity...');
      
      The table is created as −
      TUTORIAL_TITLE
      DESCRIPTIONS
      MySQL Tutorial
      MySQL is an RDBMS that uses SQL to structure the data stored
      ORACLE Tutorial
      ORACLE is an RDBMS that uses SQL to structure the data stored
      MySQL Security
      MySQL Database can store sensitive data, so security is required
      MySQL vs MariaDB
      Comparing two databases...
      JDBC Tutorial
      In this Java-based database connectivity...
      Using the Query Expansion Mode in full-text search, we search for records of articles relevant to data, with the keyword ‘RDBMS’
      SELECT * FROM DBMS_TUTORIALS 
      WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
      AGAINST ('RDBMS' WITH QUERY EXPANSION);
      

      Output

      The output is obtained as −
      TUTORIAL_TITLE
      DESCRIPTIONS
      ORACLE Tutorial
      ORACLE is an RDBMS that uses SQL to structure the data stored
      MySQL Tutorial
      MySQL is an RDBMS that uses SQL to structure the data stored
      MySQL Security
      MySQL Database can store sensitive data, so security is required
      MySQL vs MariaDB
      Comparing two databases...
      JDBC Tutorial
      In this Java-based database connectivity...

      IN NATURAL LANGUAGE MODE

      In the result-set obtained above, all tutorial records are about databases, which is why the query retrieved all the records ordered based on relevance.
      SELECT * FROM DBMS_TUTORIALS 
      WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
      AGAINST ('Security' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
      

      Output

      The output is obtained as −
      TUTORIAL_TITLE
      DESCRIPTIONS
      MySQL Security
      MySQL Database can store sensitive data, so security is required
      JDBC Tutorial
      In this Java-based database connectivity...
      MySQL Tutorial
      MySQL is an RDBMS that uses SQL to structure the data stored
      ORACLE Tutorial
      ORACLE is an RDBMS that uses SQL to structure the data stored
      MySQL vs MariaDB
      Comparing two databases...
      In this result-set, even if the search keyword is 'Security', the actual security related tutorials are just 'MySQL Security' and 'JDBC Tutorial', so they are retrieved first. These records are then followed by database related records as an expanded query.

      Query Expansion Full-Text Search Using Client Program

      We can also Perform Query expansion full-text search operation using the client program.

      Syntax

      PHPNodeJSJavaPython
      To perform the Query Expansion Full-Text Search through a PHP program, we need to execute the SELECT statement using the mysqli function query() as follows −
      $sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
      $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.');
      /*CREATE Table*/
      $sql = "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
      $result = $mysqli->query($sql);
      if ($result) {
          printf("Table created successfully...!\n");
      }
      //insert data
      $q = "INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES 
      ('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored'),
      ('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored'),
      ('MySQL Security', 'MySQL Database can store sensitive data, so security is required'),
      ('MySQL vs MariaDB', 'Comparing two databases...'),
      ('JDBC Tutorial', 'In this Java-based database connectivity...')";
      if ($res = $mysqli->query($q)) {
          printf("Data inserted successfully...!\n");
      }
      //Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword 'RDBMS'
      $s = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
      if ($r = $mysqli->query($s)) {
          printf("Table Records: \n");
          while ($row = $r->fetch_assoc()) {
              printf("Tutorial_title: %s, Descriptions: %s", $row["TUTORIAL_TITLE"], $row["DESCRIPTIONS"]);
              printf("\n");
          }
      } else {
          printf('Failed');
      }
      $mysqli->close();
      

      Output

      The output obtained is as shown below −
      Table created successfully...!
      Data inserted successfully...!
      Table Records:
      Tutorial_title: ORACLE Tutorial, Descriptions: ORACLE is an RDBMS that uses SQL to structure the data stored
      Tutorial_title: MySQL Tutorial, Descriptions: MySQL is an RDBMS that uses SQL to structure the data stored
      Tutorial_title: MySQL Security, Descriptions: MySQL Database can store sensitive data, so security is required
      Tutorial_title: MySQL vs MariaDB, Descriptions: Comparing two databases...
      Tutorial_title: JDBC Tutorial, Descriptions: In this Java-based database connectivity...