- MariaDB - Useful Functions
- MariaDB - Backup Loading Methods
- MariaDB - Backup Methods
- MariaDB - SQL Injection Protection
- MariaDB - Managing Duplicates
- MariaDB - Sequences
- MariaDB - Table Cloning
- MariaDB - Temporary Tables
- Indexes & Statistics Tables
- MariaDB - Alter Command
- MariaDB - Transactions
- MariaDB - Regular Expression
- MariaDB - Null Values
- MariaDB - Join
- MariaDB - Order By Clause
- MariaDB - Like Clause
- MariaDB - Delete Query
- MariaDB - Update Query
- MariaDB - Where Clause
- MariaDB - Select Query
- MariaDB - Insert Query
- MariaDB - Drop Tables
- MariaDB - Create Tables
- MariaDB - Data Types
- MariaDB - Select Database
- MariaDB - Drop Database
- MariaDB - Create Database
- MariaDB - Connection
- MariaDB - PHP Syntax
- MariaDB - Administration
- MariaDB - Installation
- MariaDB - Introduction
- MariaDB - Home
MariaDB Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
MariaDB - Indexes & Statistics Tables
Indexes are tools for accelerating record retrieval. An index spawns an entry for each value within an indexed column.
There are four types of indexes −
Primary (one record represents all records)
Unique (one record represents multiple records)
Plain
Full-Text (permits many options in text searches).
The terms “key” and “index” are identical in this usage.
Indexes associate with one or more columns, and support rapid searches and efficient record organization. When creating an index, consider which columns are frequently used in your queries. Then create one or multiple indexes on them. In addition, view indexes as essentially tables of primary keys.
Though indexes accelerate searches or SELECT statements, they make insertions and updates drag due to performing the operations on both the tables and the indexes.
Create an Index
You can create an index through a CREATE TABLE...INDEX statement or a CREATE INDEX statement. The best option supporting readabipty, maintenance, and best practices is CREATE INDEX.
Review the general syntax of Index given below −
CREATE [UNIQUE or FULLTEXT or...] INDEX index_name ON table_name column;
Review an example of its use −
CREATE UNIQUE INDEX top_sellers ON products_tbl product;
Drop an Index
You can drop an index with DROP INDEX or ALTER TABLE...DROP. The best option supporting readabipty, maintenance, and best practices is DROP INDEX.
Review the general syntax of Drop Index given below −
DROP INDEX index_name ON table_name;
Review an example of its use −
DROP INDEX top_sellers ON product_tbl;
Rename an Index
Rename an index with the ALTER TABLE statement. Review its general syntax given below −
ALTER TABLE table_name DROP INDEX index_name, ADD INDEX new_index_name;
Review an example of its use −
ALTER TABLE products_tbl DROP INDEX top_sellers, ADD INDEX top_2016sellers;
Managing Indexes
You will need to examine and track all indexes. Use SHOW INDEX to pst all existing indexes associated with a given table. You can set the format of the displayed content by using an option such as “G”, which specifies a vertical format.
Review the following example −
mysql > SHOW INDEX FROM products_tblG
Table Statistics
Indexes are used heavily to optimize queries given the faster access to records, and the statistics provided. However, many users find index maintenance cumbersome. MariaDB 10.0 made storage engine independent statistics tables available, which calculate data statistics for every table in every storage engine, and even statistics for columns that are not indexed.
Advertisements