MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL Invisible Index

Invisible Index

MySQL index decides an option to the availability of the index for the query optimizer. This index shows the visible and invisible options to displays the index column in the table. MySQL maintains the invisible indexes and keeps them updated when the data in the columns associated with the indexes changes.

MySQL management system provides a visible index by default. The invisible index supports to privacy and safety of the key columns.

Syntax

The basic syntax of the invisible index shows below.

 CREATE TABLE table_name(
 Column1data type NOT NULL PRIMARY KEY,
 Column2 data type VARCHAR (10) NOT NULL,
 INDEX (index name)INVISIBLE); 

The following points should be remembered while using this syntax:

  • The primary key column does not apply an invisible index.
  • The other column applies the invisible statement on the table.

The basic syntax of the invisible index shows below.

CREATE INDEX name ON table name (column1, column2, columnN) INVISIBLE;

The following points should be remembered while using this syntax:

  • Create index with the  required index name using MySQL query.
  • The "INVISIBLE" keyword requires for invisible index.

The syntax of the invisible index on the existing table shows below.

 ALTER TABLE name ALTER INDEX name INVISIBLE;
 The syntax of the visible index on the existing index shows below.
 ALTER TABLE name ALTER INDEX name VISIBLE; 

Examples of the Invisible index

1) Example: Create an invisible index by using a CREATE table command.

Execute the below query to create a new index with an invisible option. 

 mysql> create table index_table(
 roll_number INT NOT NULL PRIMARY KEY,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 img blob NOT NULL,
 INDEX (name)INVISIBLE); 

OUTPUT

Execute the below query to show the index on the table.

mysql> show index from index_table;
Invisible Index

The above image shows an index of the table. The name column assigns for the invisible index.  Here, the visibility shows a "NO" value.

2) Example: Create an invisible index with an existing table.

Execute the below query to create a new index with an invisible option. 

mysql> CREATE INDEX mark ON index_table (mark (100)) INVISIBLE;

OUTPUT

Execute the below query to show the index on the table.

mysql> show index from index_table;
Invisible Index

The above image shows three rows of invisible indexes. It contains the key name, column name, index type, and visibility of the table. Here, the mark column visibility shows the "NO" value with prefix.

3) Example: Create an invisible index with an existing index.

Execute the below query to update the index with the invisible option.

mysql> ALTER TABLE index_table ALTER INDEX name INVISIBLE;

OUTPUT

Execute the below query to show the index on the table.

mysql> show index from index_table;

Invisible Index

The above image shows three rows of invisible indexes. The table shows the key name, column name, index type, visibility of the table. The name isassigned with NO value in the visible column.

4) Example: Create a visible index with the existing index.

Execute the below query to update the index with the invisible option.

mysql> ALTER TABLE index_table ALTER INDEX name VISIBLE;

OUTPUT

Execute the below query to show the index on the table.

mysql> show index from index_table;

Invisible Index

The above image shows three rows of invisible indexes. It contains the key name, column name, index type, and visibility of the table. Here, the name column is converted from an invisible to visible option.