MySQL Indexing, Different types and when to use them?

Question

Ok, So I have (re)searched a lot regarding MySQL Indexes and their importance and I understand i have to use it to make database queries run faster.,

And i know the syntax to add index on any filed.

But here's what i don't understand, ( I am using Heidi SQL to manage database locally )

I have a table with the following fields

id
company_id
author_id
client_id
project_id
title
description
status
date

Here, id is the primary key, auto incremented and is already indexed.

I want to add an index on company_id, author_id, client_id, project_id, But i have few different options here (Heidi SQL: select field, right click, create new index ) Key, Unique, Full Text, Spatial

I know (guess) Key will just index the field, Unique will make sure that the field has to be unique and Full Text index will be best if i plan to perform search on that field.

Question 1 : What does Spatial do, where and when should i use this index.

Question 2 : While creating and index, I have 2 options, Either create a new index or Add to an already existing index (like the name of primary field).

What's the difference between creating a new index OR adding to an already existing index? Is creating an index under different name for every different field i index a good idea or should i create/add all the indexes under the same name.

Thanks for you time.

Answer 1

You should start with a simple Key index. If you add Unique that can have unintended consequences, for example enforcing uniqueness on a column like company_id would definitely not work (lots of rows will share the same company_id).

A Spatial index is only for geo-coding data (latitude/longitude values), so this does not apply to any of these. Fulltext index is used when you want to search by words within the fields, it also will not apply to numeric values that you have. Further, a Fulltext index is only available in MyISAM, not the INNODB transactional engine, so if you ever want to migrate the table this would be a barrier to that.

If you add to an existing index, you are making a "compound" index on more than one column. Typically this is not a good idea, unless you are trying to enforce uniqueness in an index on a column that is not already unique. For example, you could index: company_id + id, and make it unique in that case. However, the index takes more space and will be slower on writes.

In summary, you should just use a normal KEY index on the columns you want to search by. MySQL will only use one of the indexes for each query, to see how it is actually accessing the data you can use the EXPLAIN utility. You want to make sure it is using an index in an effective way to narrow down the number of rows it needs to return or search for best performance.