Lecture 10 - Indexes and Views (1) Lecture Notes PDF

2 INDEX  TOPIC Define index and views in records retrieval Analyze the advantages of creating views Analyze th advances of using indexes Use views to retrieve records Use the index to retrieve records  Purpose of MySQL Index  Imagine flipping through a giant...

2 INDEX  TOPIC Define index and views in records retrieval Analyze the advantages of creating views Analyze th advances of using indexes Use views to retrieve records Use the index to retrieve records  Purpose of MySQL Index  Imagine flipping through a giant phonebook without an index. Indexes in MySQL act like helpful tools, speeding up data searches.  By organizing data efficiently, they help retrieve specific information quickly, making your queries run like a charm.  Without MySQL index MYSQL must begin the search on the first row and read through the entire table to find  Purpose of MySQL Index designed tools to increase the speed and efficiency of data retrieval operations within a database. MySQL indexes let the database quickly find and retrieve your required data without scanning the whole table. can execute high-performance database queries that render faster and, subsequently, a more responsive application with the strategic implementation of indexes.  What is MySQL Index  a data structure that improves the speed of data retrieval operations on a database table.  Similar to index of the book, helps to find quickly specific information without reading the whole book  MySQL index allows the database to locate and access rows in a table much faster than, when without an index  Types of MySQL Indexes  MySQL supports a variety of INDEXES can perform different functions in the events of different use cases. Types of MySQL Indexes 1.Simplex Index 2.Primary Index 3.Unique Index 4.Full-Text Index 5.Descending Index 6.Composite Index  Types of MySQL Indexes 1.Simplex Index basic type of index where the values inserted into the column, containing this index, are searched easily. Ex: duplicate values or NULL 2. Primary Index Must always contain unique values and values must not be null Primary key can be a set to a single column of a database or multiple column as well Ex: Employee Number, SSS No.  Types of MySQL Indexes 3. Unique Index Does not allow duplicate values to be inserted into a table column(where index define). Added to single or multiple columns of tables. If it is added to a single column, the values of that column must be unique But if it is added to multiple column, the combination of values in these columns must be unique. Example: Product Number, product code, airwaybill, packing number,  Types of MySQL Indexes 4. FullText Index Does not allow duplicate values to be inserted into a table column(where index define). Added to single or multiple columns of tables. If it is added to a single column, the values of that column must be unique Comes with full-text indexes with MATCH and AGAINST clause in SQL series, find in search engines Example: Product Description, Articles  Types of MySQL Indexes 5. Descending Index descending index is only available in MySQL versions after 8.0. It is simple index used to store data in a reverse order. Using this index, it is easy to search for the latest values inserted into the database table.  Types of MySQL Indexes 6. Composite Index an index created on more than one column. Performance is aided in queries that filter on more than one column, and it takes the first place in the column used in the query. Example, suppose you always search by last_name and first_name; then, a composite index on (last_name, first_name) would come in handy. Ex: Website of Kdrama : title of Kdrama and Artist  Types of MySQL Indexes 7. Spatial Index Applied to geographic data types and enable the effective management and querying of spatial data. Index is useful in application that involves mapping, location-based services and geographic information system  Creating INDEXEX on New Syntax: Table CREATE TABLE ( column1 datatype PRIMARY KEY, column1 datatype PRIMARY KEY, column1 datatype PRIMARY KEY, ……. INDEX (column_name) ); Example: Creating INDEXEX on New Table CREATE AN INDEX  Example: Creating INDEXEX on New Table To Populate ind_customers INSERT INTO ind_customers (cust_no, f_name,l_name, age, address, salary) VALUES (001,'Maria Theresa', 'De Leon', 25, 'Shaw Blvd, Mandaluyong City', 38000.00); VALUES (002,'Leonora Marie ', 'Bautista', 305, 'JP Rizal Extension , Makati City', 42000.00); VALUES (003,'Josephino ', 'Dela Cruz', 55, 'Narra Street GreenMeadows, Quezon City', 55000.00);  Creating INDEXEX on Existing To create an indexTable on existing table, we use the following SQL statements − 1. With CREATE INDEX statement 2. With ALTER command Basic syntax of the CREATE INDEX statement Syntax: CREATE INDEX name_INDEX ON table_name; CREATE INDEX l_name on ind_customers(lname);  Creating INDEXEX on Existing Syntax: Table CREATE INDEX name_INDEX ON table_name; CREATE INDEX l_name on CREATE AN ind_customers(lname); INDEX  Creating INDEXEX on Existing Table ALTER … ADD COMMAND Syntax: ALTER TABLE tbl_name ADD INDEX index_name(column_list); ALTER TABLE ind_customers ADD INDEX ind_customers(age); CREATE AN INDEX  1) Simple and Unique Index A unique index is the one which cannot be created on two rows at once. Syntax: CREATE UNIQUE INDEX INDEX_NAME ON table_name(column1, column2 …);  1) Simple and Unique Index (cont..) Populate uni_products table  2) Create table with multi-column with Unique INDEX A Library database where each book is identified by a combination of its title and author  2) To ADD UNIQUE index with EXISTING TABLE - To populate  2) To ADD UNIQUE index with EXISTING TABLE - Syntax: ALTER TABLE tbl_name ADD UNIQUE INDEX index_unique_columns (col1, col2,...); Why An ERROR?  2) REMOVE UNIQUE INDEX IN THE TABLE - To REMOVE UNIQUE index of the table Syntax: ALTER TABLE tbl_name DROP INDEX index_unique_column; ange the unique index from book_title to author_na  can be created only for CHAR, VARCHAR, or TEXT columns. 4) FULL-TEXT INDEXES - Allows us to search for a text-based data stored in database - Before running a full-text search in column(s) of table , must create a full-text index on those columns MySQL Full-Text Search - To perform full-text Search on MySQL, use MATCH( ) and AGAINST() function in a WHERE Clause of an sql SELECT Statement. Note: Stop words are words that are commonly used (such as 'on', 'the', or, 'it') in sentences and will be ignored during the searching process.  4) FULL-TEXT INDEXES (cont..) Syntax: SELECT column_name(s) FROM table_name WHERE MATCH(col1, col2,...) AGAINST(expression [search_modifier]) WHERE: 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.  can be created only for CHAR, VARCHAR, or TEXT columns. 4) FULL-TEXT INDEXES (cont..) - Can be created only for CHAR, VARCHAR OR TEXT COLUMN - Can be given in the CREATE TABLE statement when table is created or added later using ALTER TABLE or CREATE INDEX. - Either InnoDB or MYISAM Tables use the full-text indexes. - Minimum length for word for full-text search is three(3) for InnoDB databases and four(4) characters for MyISAM tables  can be created only for CHAR, VARCHAR, or TEXT columns. 4) FULL-TEXT INDEXES (cont..) - Can be given in the CREATE TABLE statement when table is created or added later using ALTER TABLE or CREATE INDEX. - Without FULLTEXT index, it is faster to load large data sets into a table than, to load data into a table which as an existing FULLTEXT INDEX. - Therefore it is recommended to create an index before loading data.  can be created only for CHAR, VARCHAR, or TEXT columns. 4) FULL-TEXT INDEXES (cont..) Three (3) type of full-text searches 1.Natural Language Full-Text Searches -Allows the user to enter the search query in a natural human language without any special characters or operators. -Search Engine will examine query entered by the user and returns the relevant results based on the user’s intent  can be created only for CHAR, VARCHAR, or TEXT columns. 4) FULL-TEXT INDEXES (cont..) Three (3) type of full-text searches 2. Booloean Full-Text Searches -Allows to perform a full-text search based on very complex queries in the Boolean mode along with Booloean operators such as +,-, >, >, ( ) , ~, *, “ “ 3. Query Expansion Searches -Expands the user’s query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion  4) FULL-TEXT INDEXES (cont..) Three (3) ways of Creating MYSQL FULLTEXT Index 1.Using the FULLTEXT keywords 2.Using the ALTER TABLE statement 3.Using the CREATE INDEX statement Syntax 1.Using the FULLTEXT keywords CREATE TABLE table_name ( column1 data_type, column2 data_type,..., FULLTEXT (column1, column2,...) );  4) FULL-TEXT INDEXES (cont..) Syntax 1.Using the FULLTEXT keywords CREATE TABLE table_name ( column1 data_type, column2 data_type,..., FULLTEXT (column1, column2,...) );  4) FULL-TEXT INDEXES (cont..) To Populate  4) FULL-TEXT INDEXES (cont..) Using ALTER TABLE statement ALTER TABLE table_name ADD FULLTEXT (column1, column2,...) let us retrieve all the rows from the KDRAMA table where the K_TITLE or K_DIRECTOR column matches the string ‘Woo'. Result  4) FULL-TEXT INDEXES (cont..) Using DROP TABLE statement Syntax: ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE KDRAMA DROP INDEX (k_title,k_director);  4) FULL-TEXT INDEXES (Natural Language Using DROP TABLE statement) Syntax: ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE KDRAMA DROP INDEX (k_title,k_director); 4) FULL-TEXT INDEXES (cont..) another example  4) FULL-TEXT INDEXES (cont..) Boolean Full-Text Search , the Boolean full-text search in MySQL looks for specific words. IN BOOLEAN MODE modifier in the AGAINST expression. Syntax SELECT column_name(s) FROM table_name WHERE MATCH(target_column_names) AGAINST(expression IN BOOLEAN MODE); Where: The target_column_names are the names of the columns that we want to search the keyword in. The expression is the list of keywords with the Boolean operators.  4) FULL-TEXT INDEXES (cont..) Boolean Full-Text Search MySQL Boolean Full-Text Search Operators Operator Description + Include, the word must be present. Exclude, the word must not be - present. Include, the word must be present, > and have a higher priority. Include, the word must be present, < and have a lower priority. () Groups words into subexpressions  4) FULL-TEXT INDEXES (cont..) Boolean Full-Text Search MySQL Boolean Full-Text Search Operators  4) FULL-TEXT INDEXES (cont..) Boolean Full-Text Search To run data In the following query, we are searching for the rows that contains the word ‘data’ but not ‘sets’ −  4) FULL-TEXT INDEXES (cont..) Boolean Full-Text Search To run data In the following query, we are searching for the rows that contains the word ‘data’ but not ‘sets’ −  COMPOSITE INDEX  a combination of two or more than two columns in a table that allows us to identify each row of the table uniquely  MySQL allows the user to create a composite index which can consist of up to 16 columns.  The query optimizer uses the composite indexes for queries which will test all columns in the index.  guaranteed the uniqueness of the column only when they are combined.  COMPOSITE INDEX (CONT..) Any key such as primary key, super key, or candidate key can be called composite key when they have combined with more than one attribute. useful when the table needs to identify each record with more than one attribute uniquely column used in the composite key can have different data types. 2 Ways to add composite 1.Using CREATE statement 2.Using ALTER statement  COMPOSITE INDEX (CONT..) 1) Composite using CREATE 2 (two) Ways to add composite 1.Using CREATE statement 2.Using ALTER statement COMPOSITE INDEX (CONT..)  COMPOSITE INDEX (CONT..) 2) Composite using ALTER - Use to do modification on tables of an existing tables. -Requires to add composite index to uniquely identify each record of the table with more than one attributes  COMPOSITE INDEX (CONT..) To alter the table - ALTER TABLE Student add primary key(stud_id, sub ect); END! THANKS FOR LISTENING.  Index exercises https://www.digitalocean.com/community/tutorials/how-to-use- indexes-in-mysql#connecting-to-mysql-and-setting-up-a-sample- database https://www.w3resource.com/mysql-exercises/northwind/products- table-exercises/index.php (mysql exercises)  Reference  https://www.geeksforgeeks.org/mysql-indexes/  https://www.geeksforgeeks.org/drop-multiple- tables-in-mysql/?ref=next_article  WHOLE SQL CODE: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql- programming-sql-server/temporary-tables-in-sql-server/#:~:text=If %20you're%20interested%20in,you%20invoke%20them%20from %20TempDB.&text=or%20you%20can%20find%20them,of %20TempDB%20without%20swithching%20databases. https://www.javatpoint.com/mysql-composite-key