use Foreign key indexes in multi index mysql

Question

I have A table with almost 20 fields which several of those are Foreign Key that already has been indexed by Mysql, now I want to create a multi-indexes index that it contains 3 FK field, First tried was based on Fields

ALTER TABLE `Add`
    Add INDEX `IX_Add_ON_IDCat_IDStatus_IDModeration_DateTo_DateAdded` 
       (`IDCategory`,`IDStatus`,`IDModeration`,`DateTo`,`DateAdded`);

But I think it's better to have an index on indexes instead of fields but my following effort faced with error: Error Code: 1072. Key column 'FK_Add_Category' doesn't exist in table

ALTER TABLE `Add`
    Add INDEX `IX_Add_ON_IDCat_IDStatus_IDModeration_DateTo_DateAdded`
        (`FK_Add_Category`,`FK_Add_AddStatus`,`FK_Add_AddModeration`,
         `IX_Add_DateTo`,`IX_Add_DateAdded`);

My question is is it possible to add an index on exists Indexes ( FK index in my case ) or not and there is the only way to create an index on Columns? if yes How I create that?

You can index on table name (index column name) , an index is not a column. — P.Salmon
– P.Salmon, Commented Sep 16, 2017 at 11:42
Possible duplicate of Adding multiple indexes at same time in MySQL — Dr. X
– Dr. X, Commented Sep 16, 2017 at 11:44
What is the question? The first statement is the right thing to do in MySQL. — Gordon Linoff
– Gordon Linoff, Commented Sep 16, 2017 at 11:45
@zhilevan . . . There is no "yet". "Index on index" does not make sense in a SQL context. — Gordon Linoff
– Gordon Linoff, Commented Sep 16, 2017 at 13:31

Rick James · Accepted Answer · 2017-09-16 19:07:29Z

An index is an ordered list of values. It is used to make it more efficient to find rows in the table.

Think about the common, real-life, example of INDEX(last_name, first_name). It makes it easy to look up someone if you have their last name and first name. And sort of easy if you have only their last name.

But it is useless if all you have is their first name.

FOREIGN KEYs necessitate a lookup. Apparently you have a FK to AddStatus, since I see FK_Add_AddStatus. That FK generated a lookup for AddStatus. Think of that as being like a separate index on first_name. It is totally separate from the index on last_name & first_name.

5 columns is usually too many to put into a single index.

MySQL uses only one index for a given SELECT.

So, now, I ask, what SELECT might use that 5-column index? Please show us it. We can discuss whether it is useful, and whether the columns are in the optimal order.

Collectives™ on Stack Overflow

use Foreign key indexes in multi index mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest