MySQL INDEXES

Hi all, from this post I’m going  to talk about another optimization technique used in MySQL, indexing. We can use indexing with tables to make our queries more efficient. Let’s see what is indexing? Practically, indexes are also type of tables, which keep primary key or index field and a pointer to each record into the actual table. Indexes are not visible to the users. But they will increase the searching speed of a table. But the downside of indexing is it will increase the inserting, updating, deleting times as well. Because it has to update the indexes as well.

Let’s see actually it behaves like that or not.

• First I’m creating a table without indexing.

• Then I’m inserting data to that table. It took 0.04 seconds to complete the query.

• Next I want to retrieve data from the table. It took 0.02 seconds to complete the query.

• Finally I’m updating some records. It took 0.05 seconds to complete the query.

• Now let’s create another table with same fields, but this time with indexing.

• Then I’m inserting same set of data to the table. It took 0.04 seconds to complete the query.

• Retrieving the same data set. It took 0.00 seconds to complete the query.

• Updating the same record. It took 0.06 seconds to complete the query.

• So you can see that there is a clear time reduction in the select query and time increase in the update query as well.

If you are using data base tables in your application for searching more than other things, then indexing can help you. There are 4 types of indexing techniques are used,

1. Primary Key
2. Unique
3. Index
4. Full Text

PRIMARY KEY

This is the use of a PRIMARY KEY in a table as indexing. This is what we talked earlier.

• How can we make sure that indexes has been created.
• You can use the following query. Here data is not readable.

• So I’m using the ‘\G‘, vertical format output, to make it more readable.

• Let’s check it for the table without the indexing. It’s an empty set.

UNIQUE

This is the use of a UNIQUE key field. If we use UNIQUE, then there can’t be any repeating values.

• Let’s see how to add a UNIQUE index to the previously created table.

• Let’s check for the index.

• If we want we can use two columns as the index field as well.

• Then it will show both columns as key indexes.

INDEX

Here we are using the INDEX. Here we can use repeating values as the index as well.

• Let’s create a new table with INDEX.

• Now let’s check the index.

• Next try to add data.

• Then we can retrieve data.

• Let’s update some data.

FULL TEXT

We can use the FULL TEXT with fields with text values such as VAR CHAR, CHAR, TEXT.

• Before adding the new index I want to delete the previously created index. You can simple DROP it.

• Let’s check whether it has been dropped or not.

• Let’s try to create an index for a field with INT value.

• So now create it for a text field.

• Let’s check it.

• If you want you can create the indexes in the descending order of the column which we are using. Just add DESC at the end of the column name.

That’s how to create, update, delete indexes to a table. There is another way to check the use of INDEXES. We can use the EXPLAIN with a query as well.

That’s all about indexing. Hope now you have a clear idea about indexing. See you soon with another interesting topic. Thank You!