DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Recover Distributed Transactions in MySQL
  • Enhancing Database Efficiency With MySQL Views: A Comprehensive Guide and Examples
  • Seamless Transition: Strategies for Migrating From MySQL to SQL Server With Minimal Downtime
  • How To Convert MySQL Database to SQL Server

Trending

  • The Cypress Edge: Next-Level Testing Strategies for React Developers
  • Zero Trust for AWS NLBs: Why It Matters and How to Do It
  • How to Convert XLS to XLSX in Java
  • Why Documentation Matters More Than You Think
  1. DZone
  2. Data Engineering
  3. Databases
  4. Unused Indexes in MySQL: A Basic Guide

Unused Indexes in MySQL: A Basic Guide

In MySQL, unused indexes waste space and make queries slower. This guide will show you how to identify unused indexes and avoid creating them.

By 
Everett Berry user avatar
Everett Berry
·
Sep. 14, 21 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
3.7K Views

Join the DZone community and get the full member experience.

Join For Free

Unused Indexes in MySQL: A Basic GuideIntroduction

Properly designing and using indexes available in multiple flavors of MySQL can be a challenge due to the variety of their types and all of the nuances unique to them. However, don’t fret – this blog post should act as a decent starting point for people wanting to learn how MySQL uses indexes on a deeper level.

What Are Indexes and When Should We Use Them?

In their most basic form, database indexes act as a list of records—records that have a reference to a database structure in which they reside. Indexes are mostly used to eliminate data from consideration, and by doing so, make queries faster.  Without an index, a database must read all data that exists in a table one row at a time, meaning that if our table has, say, 100,000 records, a database must read through the majority of them until it finds the row suitable to us. With an index, however, a database can eliminate certain rows from consideration, scan through fewer rows, and return results faster.

Indexes have multiple types – each of them is suitable for different things; for example, B-Tree indexes might suit us well if our primary queries use the equality operator (=), more than (>), more than or equal to (>=), less than (<), less than or equal to (<=) operators or BETWEEN keywords, and, in some specific cases, LIKE queries. 

Prefix indexes might be useful when the data stored inside our columns is pretty large, and as such, indexing the entire column might be out of the question, etc. Since we have already covered some of the subtleties of indexes in MySQL, we won’t go too much into detail in this blog post, but understanding the following should help:

Scenario

Would Indexes Help Us?

Our application is primarily running SELECT queries. Yes
We frequently update, delete or insert data to or from our database without doing many read operations. No
We are dealing with big data sets and want to search through the data (for example, we want to build a search engine that would allow us to search through huge troves of data.) Yes
We are dealing with big data sets and our application acts as an archive for information without using search features. No


Above, you can see some of the most basic and frequent scenarios where developers could use indexes. The general idea about indexes is that indexes speed up SELECT queries while slowing all of the other types of queries down – they slow other types of indexes down because the database needs to do some extra work – for example, when inserting data into the table, the database has to add a new record to every index on a certain table. Multiply that by the number of indexes you have, and you already see how quickly the performance of some types of queries could degrade.

Now that we have gone through some of the basics, let’s get down to business – what can we do to make our indexes actually necessary to MySQL?

Making Sure MySQL Uses Indexes

If we want to make sure our MySQL instances use the indexes we feed them, we have a couple of options:

Option

Information
Manual approach Involves manually inspecting the table structure, the information regarding indexes through phpMyAdmin or other management tools, etc.
Using EXPLAIN queries provided by MySQL EXPLAIN queries would provide us with relevant information that would help us figure out whether our queries use indexes or not. We would need to look into the possible_keys, key, and key_length columns. The possible_keys column would display all of the possible indexes MySQL was able to choose from, the key column would display the index actually chosen, and the key_length column would display the length of the index chosen by MySQL.


To create indexes that would be useful for your MySQL instances, one of the things you need to take care of are the index types that you are going to use – however, you also need to keep an eye on how many indexes are you going to use and create a tradeoff between the slow performance of INSERT, UPDATE, and DELETE queries and faster performance of SELECTs that would be acceptable to you. To do that, first, add an index to your MySQL instance – Arctype can be of assistance here as well – navigate towards the table you want to add indexes on towards the left-hand side and click “Edit Table”: if you want to add a unique index to it, for example, click the button underneath the Constraints and edit them (in this case, checking “Unique” should do the trick):

Creating Indexes For MySQL Instances,

Once you‘re done, click Apply, and your changes should be visible on your database instances:

Indexes For MySQL Instances,

Arctype will also kindly let you know once your changes are live:

Database Instance

Once you have added relevant indexes to your database, it’s time to let EXPLAIN do its magic. Connect to your database instance, then add EXPLAIN in front of your query to see what it does and whether your query uses the indexes you defined or not:

MySQL
 
EXPLAIN SELECT * FROM arctype WHERE demo_column = 'Demo Data';
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | arctype | NULL | const | demo_column | demo_column | 602 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


To get the best chances of making your column useful to MySQL, you would need to complete the following steps:

  1. Only query the column that you have indexed – doing so will attract the best chances of success. In other words, make sure the column you are querying is isolated – leave everything after the WHERE clause alone and do not mix anything into it, also avoid making an indexed column a part of an expression or a function within a query. As an example, avoid issuing queries like SELECT * FROM demo_table WHERE column_1 = ‘Demo’ AND column_2 = ‘Demo 2’; if column_2 is not indexed and only column_1 is, etc.
  2. Look for the "Using index" value in the Extra column – that’s MySQL telling us whether the index is being used or not.
  3. If you want to look into everything further, be sure to explore the possible_keys, key, and key_len columns as well: the possible_keys column depicts which indexes MySQL could choose from, the key column covers the index that was actually chosen and the key_len depicts the length of the index (in other words, the key).  Since you no longer worry about the SQL clients you use to run your queries on (you’re already using Arctype, aren’t you?), that’s not very complex.

Index Corner-Cases

In general, by now, you have probably understood that to make your indexes necessary, you should choose their types very carefully and also, where possible, use EXPLAIN queries, meaning that the aforementioned advice should put your databases and your indexes on a good path. However, a big part of indexes becoming obsolete and unnecessary is that MySQL is not very picky; it will generally take anything we throw at it. The same pertains to indexes – if you have 10 rows and decide to index them all, you can, and MySQL will definitely be able to complete your request, but what’s the point? In that case, adding an index would be a waste of disk space.

You should avoid always resorting to indexes to improve performance – partitions and other features can do some wonderful work as well. If that doesn’t solve all of your issues, consider normalizing your database schemas – that should get you one step closer to database perfection. If that doesn’t get you very far either, consider exploring both the MySQL’s and MariaDB’s documentation: that should do the trick.  Happy indexing!

Database MySQL sql

Published at DZone with permission of Everett Berry. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Recover Distributed Transactions in MySQL
  • Enhancing Database Efficiency With MySQL Views: A Comprehensive Guide and Examples
  • Seamless Transition: Strategies for Migrating From MySQL to SQL Server With Minimal Downtime
  • How To Convert MySQL Database to SQL Server

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!