Indices in sql
Donald Green
What is an index?
Nobody likes slow systems.
High system performance is paramount in almost all database systems.
Most businesses invest heavily in hardware to make finding and manipulating data faster.
But there are limits to the investment in equipment a business can make.
Optimizing your database is the cheaper and better solution.
For this we can use INDEXES.
- Slow response times are usually due to records being stored randomly in database tables.
- Search queries must cycle through all the randomly saved records one by one to find the desired data.
- This results in poor database performance when it comes to retrieving data from large tables.
- Indexes come in handy in such situations. Indexes sort data in an organized, sequential way. Think of the index as an alphabetically sorted list. Names that have been sorted alphabetically are easier to search than those that are not sorted.
- Indexes are created on the columns that will be used to filter the data.
- Using indexes on tables that are updated frequently can degrade performance. This is because MySQL creates a new index block each time data is added or updated in a table. In general, indexes should be used on tables whose data does not change frequently but is used frequently in individual search queries.
Create Basic Index Syntax
Indexes can be defined in two ways
1. During table creation
2. After the table has been created
Example:
For our myflixdb, we expect a lot of database lookups for the full name.
We will add the "full_names" column to the index in the new "members_indexed" table.
The script shown below helps us achieve this.
CREATE TABLE `members_indexed` ( `membership_number` int(11) NOT NULL AUTO_INCREMENT, `full_names` varchar(150) DEFAULT NULL, `gender` varchar(6) DEFAULT NULL, `date_of_birth` date DEFAULT NULL, `physical_address` varchar(255) DEFAULT NULL, `postal_address` varchar(255) DEFAULT NULL, `contact_number` varchar(75) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY(`membership_number`),INDEX(full_names) ) ENGINE=InnoDB;
Execute the above SQL script in MySQL workbench against "myflixdb".
Myflixdb update shows a newly created table called members_indexed.
The » note « members_indexed table has "full_names" in the indexes node.
As the item base expands and the number of records increases, lookups on the members_indexed table using WHERE and ORDER BY clauses will be much faster than those using the members table without a defined index.
Add Basic Index Syntax
The above example created an index when defining a database table. Let's say we already have a certain table and lookups to it are very slow. They take too long to return results. After investigating the problem, we find that we can greatly improve system performance by creating an INDEX for the most commonly used column in the WHERE clause.
We can use the following query to add an index
CREATE INDEX id_index ON table_name(column_name);
Let's assume that search queries in the movies table are very slow and we want to use an index on "movie title" to speed up the queries, for this we can use the following script.
CREATE INDEX `title_index` ON `movies`(`title`);
Executing the above query creates an index on the title field in the movies table.
This means that all searches in the movie table using "title" will be faster.
However, searches against other fields in the movie table will be slower than searches based on an indexed field.
Note that you can create indexes on multiple columns if needed, depending on the fields you intend to use for your database search engine.
If you want to view the indexes defined for a particular table, you can use the following script to do so.
SHOW INDEXES FROM table_name;
Let's now look at all the indexes defined on the movies table in myflixdb.
SHOW INDEXES FROM `movies`;
Running the above script in MySQL Workbench against myflixdb gives us the following results shown below.
Note that the table's primary and foreign keys are already indexed by MySQL. Each index has its own unique name, and the column for which it is defined is also displayed.
Remove Basic Index Syntax
The delete command is used to delete already defined indexes on a table.
There may be times when you have already defined an index on a table that is updated frequently. You may want to drop indexes on such a table to improve the performance of UPDATE and INSERT queries. The basic syntax used to drop an index on a table is as follows.
DROP INDEX `index_id` ON `table_name`;
Let's now look at a practical example.
DROP INDEX ` full_names` ON ​​`members_indexed`;
Running the above command removes the index with id `full_names` from the members_indexed table.
Summary
- Indexes are very powerful when it comes to greatly improving the performance of MySQL search queries.
- Indexes can be defined when the table is created, or added later after the table has already been created.
- You can define indexes on more than one column in a table.
- SHOW INDEX FROM table_name is used to display specific indexes on a table.
- The DROP command is used to drop a specific index on a given table.
