Single index in Alter table wont get added if its part of a multi column index

Question

I have this inside a stored procedure so far:

IF NOT EXISTS(SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME= 'food'  AND (INDEX_NAME='ix_meat' OR COLUMN_NAME='meat')
    )
THEN
    CREATE INDEX ix_meat ON food (meat);
END IF; 


IF NOT EXISTS(SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME= 'food'  AND (INDEX_NAME='ix_greens' OR COLUMN_NAME='greens')
    )
THEN
    CREATE INDEX ix_greens ON food(greens);
END IF; 

Rest of the columns in food besides primary are:

fruits, portion, gender, weight

I want to add only a single index if there is no single index on these columns or if either of them are part of a multi column index but on the right side like (gender, greens). To be sure a single column index doesn’t already exist, I'd need to include a row count check on that column. How do I modify this script to do that?

Answer 1

Use the seq_in_index column to tell where the column is in an index. You want to add the index unless it's the first column in an index.

IF NOT EXISTS(SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME= 'food'  AND (INDEX_NAME='ix_meat' OR (COLUMN_NAME='meat' AND SEQ_IN_INDEX = 1))
    )
THEN
    CREATE INDEX ix_meat ON food (meat);
END IF; 


IF NOT EXISTS(SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME= 'food'  AND (INDEX_NAME='ix_greens' OR (COLUMN_NAME='greens' AND SEQ_IN_INDEX = 1))
    )
THEN
    CREATE INDEX ix_greens ON food(greens);
END IF;