What is Index:
Indexes are schema objects, used to locate data quickly without searching every row in a database table. It works similar to book's index page. For example, if I have to look for or locate particular word/data in a book, I will go to the book's index page and get the exact page number to get to the page. Without index page I would have gone through scanning whole book to find out the word. Similarly in database when we have huge data, to search data faster we have Indexes. Indexes do not get created automatically as it depends on user requirement of search criteria and also it has its own disadvantage well.
Why Index:
Indexes allow the database application to speed up searches.
How to create Index:
Indexes can be created using one or more column of a table.
Syntax:
CREATE INDEX <index_name> ON <table_name> <column_name>
Disadvantage:
1. It takes more time to update/Insert/Delete records in a tables with indexes as the indexes also gets updated/deleted/inserted accordingly. You should be careful while creating Index on your table.
2. It require additional space in database.
When to create Indexes:
1. Create indexes on columns (and tables) that will be frequently searched against ( the columns used for filtering or in Where condition )
2. Columns used for Join condition
3. Huge data sets are good for index creation.
4. Tables which has more fields.
Indexes are schema objects, used to locate data quickly without searching every row in a database table. It works similar to book's index page. For example, if I have to look for or locate particular word/data in a book, I will go to the book's index page and get the exact page number to get to the page. Without index page I would have gone through scanning whole book to find out the word. Similarly in database when we have huge data, to search data faster we have Indexes. Indexes do not get created automatically as it depends on user requirement of search criteria and also it has its own disadvantage well.
Why Index:
Indexes allow the database application to speed up searches.
How to create Index:
Indexes can be created using one or more column of a table.
Syntax:
CREATE INDEX <index_name> ON <table_name> <column_name>
Disadvantage:
1. It takes more time to update/Insert/Delete records in a tables with indexes as the indexes also gets updated/deleted/inserted accordingly. You should be careful while creating Index on your table.
2. It require additional space in database.
When to create Indexes:
1. Create indexes on columns (and tables) that will be frequently searched against ( the columns used for filtering or in Where condition )
2. Columns used for Join condition
3. Huge data sets are good for index creation.
4. Tables which has more fields.
Indexes are widely used in various fields, including databases, finance, economics, and more, to efficiently retrieve and organize data. Here are some common types of indexes:
Database Indexes:
- B-Tree Index: A balanced tree structure used in most relational databases for efficient data retrieval. B-tree indexes are suitable for range queries.
- Hash Index: A data structure that uses a hash function to map keys to locations, making it suitable for exact-match queries.
- Bitmap Index: Uses bitmap vectors to represent data, especially useful for columns with low cardinality, like gender or boolean values.
- Spatial Index: Optimizes queries for spatial data, such as geographic coordinates, by organizing data in a way that accelerates location-based queries.
Financial Indexes:
- Stock Market Indexes: Track the performance of a group of stocks or the overall stock market. Examples include the S&P 500, Dow Jones Industrial Average, and NASDAQ Composite.
- Bond Market Indexes: Measure the performance of a group of bonds or the bond market as a whole, such as the Bloomberg Barclays U.S. Aggregate Bond Index.
- Commodity Indexes: Reflect the performance of a basket of commodities, like the S&P GSCI (Goldman Sachs Commodity Index).
Economic Indexes:
- Consumer Price Index (CPI): Measures changes in the cost of a basket of goods and services over time, used to assess inflation.
- Gross Domestic Product (GDP): Measures the economic performance of a country by calculating the total value of goods and services produced within its borders.
Search Engine Indexes:
- Web Search Index: A database of web pages and their content, allowing search engines like Google to retrieve relevant results quickly.
- Inverted Index: Used in information retrieval systems, it maps keywords or terms to the documents in which they appear, facilitating text-based searches.
Library and Catalog Indexes:
- Library Card Catalog: An index of books and other materials in a library, often organized by title, author, subject, or other criteria.
- Online Catalogs: Digital indexes of library collections, providing search functionality for users.
Geographical Indexes:
- Geographic Information System (GIS) Indexes: Store and organize spatial data, making it possible to query and analyse geographic information.
Stock Market Volatility Index:
- VIX (CBOE Volatility Index): Measures the market's expectation of future volatility, often referred to as the "fear gauge."
Cryptocurrency Indexes:
- Crypto Market Index: Reflects the performance of various cryptocurrencies, like the CoinMarketCap Cryptocurrency Index.
These are just a few examples of the types of indexes used in different domains. Each type of index serves a specific purpose, from improving data retrieval in databases to tracking economic or financial trends.
No comments:
Post a Comment