What is index database?
- Index is a data structure used to locate and access the fastest data in database tables
- Indexing is a way to optimize database query performance by reducing the amount of access to memory when performing a query
Index database to do?
- Assuming we have a User table that stores the user’s information, we want to retrieve the information of the user whose Name field is “HauNguyen”. We have the following SQL query:
SELECT * FROM User WHERE Name = 'HauNguyen';
When there is no Index for the Name column, the query will have to run through all the rows of the User table to compare and retrieve those rows that satisfy. So when the number of records is large, this is really a nightmare @@ Index was born to solve this problem. Simply put, the index points to the data address in a table, like the Table of Contents of a book (Including the title of the page and the number of pages), which makes the query as quick as you look at the table of contents and find the right one. The page needs to read
- Indexes can be created for one or more columns in the database. Indexes are usually created by default for primary and foreign keys. In addition, we can also create indexes for columns if needed.
Structure of Index
- Index is a data structure consisting of:
- Search Key column: contains a copy of the index’s generated column values
- Data Reference column: contains a pointer to the address of the record with the corresponding index column value
Some types of Index Database
- The most common data type for Index
- Index data in B-Tree is organized and stored in the form of trees, that is, root, branch, leaf.
- The general idea of B-Tree is to store sorted values, each leaf node has the same height from the root. B-Tree can speed up queries because the storage engine does not need to find the entire table record. Instead, it will look from the root node, the root will contain the pointer to the child node, the storeage engine will rely on that pointer. It finds the right pointer by looking at the value of the node pages, which contain the range of child nodes. Finally, the storage engine indicates that the value does not exist or finds the value at the leaf node.
- B-Tree index is used in comparison expressions: =,>,> =, <, <=, BETWEEN and LIKE
- The B-Tree index is used for columns in a table when looking for a value within a certain range 2.Hash Index
- Index data is organized in the form of Key – Value linked together.
- Unlike B-Tree, the hash index should only be used in = and <> operator expressions. Do not use for search operators with a range of values such as> or <
- The ORDER BY operator cannot be optimized using the Hash index because it cannot find the next word in the Order.
- The entire contents of the Key are used to look up records values, other than the B-Tree part of the node that can also be used to search.
- Hash has faster speed than Btree type.
How to use Index Database effectively?
- Although Index plays an important role in optimizing queries and speeding up searches in the Database, the downside is that it requires more memory to store. Therefore, the Index for columns must be calculated, avoiding abuse.
- Here are some tips to help you create a more effective Database index:
- So Index columns are used in WHERE, JOIN and ORDER BY
- Use MySQL’s prefix “or” multi-columns index “function. Ex: If you create Index (first_name, last_name) then k need not create Index (first_name)
- Use the NOT NULL attribute for columns indexed
- Do not use the Index for tables that frequently have UPDATE, INSERT
- Do not use the Index for columns where values are frequently changed
- Using the EXPLAIN statement helps us know how MySQL will run the query. It shows the join order, how the tables are joined. Help the review to write optimal queries, select columns to Index easier
Some Index Database commands
- Create Index in SQL:
CREATE INDEX ten_chi_muc ON ten_bang;
- Single-Column Index in SQL: The index for a single column is an index created based on only one column in the table. The basic syntax is:
CREATE INDEX ten_chi_muc ON ten_bang (ten_cot);
- Unique index in SQL Unique index is used not only to increase performance, but also for data integrity purposes. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is:
CREATE UNIQUE INDEX ten_chi_muc ON ten_bang (ten_cot);
- Composite Index in SQL The Composite Index is an index for two or more columns in a table. Its basic syntax is as follows:
CREATE INDEX ten_chi_muc ON ten_bang (cot1, cot2);
- Implicit Index in SQL Implicit Index (can be understood as the default index) is the index that is automatically created by Database Server when an object is created. The indexes are automatically created for Primary key constraints and Unique constraints
- DROP INDEX in SQL
DROP INDEX ten_chi_muc;