CODEWITHSUNDEEP
mysqlsqlindexing
Sharikov Vladislav
Sharikov Vladislav

Reputation: 7269

How to use index in my table correctly?

I realized, that when I am creating foreign keys in table, indexes are adding automatically.

In my table:

CREATE TABLE `SupplierOrderGoods` (
    `shopOrder_id` INT(11) NOT NULL,
    `supplierGood_id` INT(11) NOT NULL,
    `count` INT(11) NOT NULL,
    PRIMARY KEY (`shopOrder_id`, `supplierGood_id`),
    CONSTRAINT `FK_SupplierOrderGoods_ShopOrders` FOREIGN KEY (`shopOrder_id`) REFERENCES `shoporders` (`id`),
    CONSTRAINT `FK_SupplierOrderGoods_SupplierGoods` FOREIGN KEY (`supplierGood_id`) REFERENCES `suppliergoods` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Index 

INDEX `FK_SupplierOrderGoods_SupplierGoods` (`supplierGood_id`)

have been created automatically.

It is okay, that index have been created as I found in another post. I was looking what indexes are used for and found, that they are used for optimizing search in tables. Now, I know, that I have to use indexes to optimize work with database.

Also, I found, that indexes can be complex (not on one field, but on some fields). In that case, I want to ask should I use complex index:

INDEX `FK_ShopOrders_SupplierGoods` (`shopOrder_id`, `supplierGood_id`),

or two simple indexes?:

INDEX `FK_SupplierOrderGoods_SupplierGoods` (`supplierGood_id`),
INDEX `FK_SupplierOrderGoods_ShopOrders` (`shopOrder_id`),

Upvotes: 0

Views: 40

Answers (1)

Caleb Palmquist
Caleb Palmquist

Reputation: 458

I'm still earning about indexes myself but I believe it's going to depend on what kind of data you will be querying the DB for.

For example, if you have a report for a certain record that will be ran a lot you'll want an index on it. If the report pulls just one column then make a one column index, if it's comprised of two, like a first name and a last name record, you'll probably want one for both.

You do not want to put an index on everything though as that can have performance issues as both the record and the index need to be updated. As such, tables that have a high amount of inserts or updating done on them you'll want to think about whether an index hurts or helps.

Lot of information to cover with indexes.

Upvotes: 3

Related Questions