Understanding MySQL Query Planner and Force Index

Understanding MySQL Query Planner and Force Index

John Hewick

To understand what MySQL is, you will first need to have a working knowledge of both database and SQL first.  In this article, we thought it would be useful to give you a better understanding of MySQL, as well as exploring some of the popular solutions used to make MySQL Query Planner use the right index.

Introduction to Databases

Did you know that you deal with data every day?  If you want to listen to your favourite song, you will often make use of a playlist on your mobile device.  In this case, your playlist is a database.  Whenever you upload a photo to one of your social channels like Facebook, the photo gallery on your profile is also a database.  Also, whenever you shop online and buy new clothes or shoes you will use the shopping cart database.  As you can see, databases are everywhere.  So how do you define a database?  Basically, a database is simply a structured collection of data.

The data will relate to each other by nature.  For example, a product will always belong to a specific product category.  This is why you will likely come across the term ‘relational database’.  In a relational database, products, categories and tags will be modelled using tables.  Your table will contain both columns and rows like a spreadsheet.  As you are dealing with large chunks of data, you want to be able to turn your data into useful information.  This is where SQL can be added into the mix.

SQL – Your Database Language

The term SQL stands for structured query language.  This is the standard language used to access any database.  Your SQL contains three parts:

  1. Your data definition language will contain statements that help you define the objects in your database.
  2. Your data manipulation language will allow you to update your database and query your data.
  3. Using data control languages also allows you to grant permissions to a user.

What Is MySQL Query Planner?

Now that you understand database and SQL, MySQL is a database management system that allows you to manage different relational databases.  Compared to other database software, MySQL is pretty easy to master and will run on various platforms including Windows, Linux, UNIX and more.  You will find it super easy to install on a desktop.  If you’re looking to develop your website or web applications, MySQL is a great choice.

How to Convince MySQL to Use the Right Index?

Unfortunately, sometimes MySQL can get it wrong.  It doesn’t use the right index.  When this happens, your MySQL will generate a query plan.  This is bad as it means any queries can take a long time to complete.  Here, we’ll give you a couple of simple solutions to solve your MySQL generating the wrong plan.

Solution #1: OPTIMISE

If MySQL got the index wrong, it may be because your table is frequently being changed. This can have a negative effect on the statistics.  You could rebuild your table to avoid this.

Solution #2: FORCE INDEX

You could negate the use the PRIMARY KEY, which will force the other EXPLAIN result.

We hope this gave you a better understanding of MySQL.  Have you used force index in the past?  Let us know in the comments below.

John Hewick

Author John Hewick

John is a full stack developer, with 10 years’ experience building websites with WordPress. Working with Elementary Digital for the last 4 years John has met all the challenges that have been sent his way.

More posts by John Hewick