0

Currently I have a MYSQL query that's under performing. I need to create an index on two fields within my query: cancel and complete. What is the best way to perform this?

SELECT* FROM table WHERE cancel != 'CANCEL' AND complete IN ('COMPLETE','TAKEN_BACK')

Improve this question

2 Answers 2

Reset to default
3

To create a multi-column index, but the column names in parentheses.

ALTER TABLE table
ADD INDEX (cancel, complete);

This is equivalent to

CREATE INDEX table_cancel_complete_ix
ON table (cancel, complete);

CREATE INDEX requires you to give a name to the index, it's optional with ALTER TABLE (it will be assigned a default name).

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

how would I apply that to the above select?
It's done automatically. If an index exists, MySQL will use it if it can.
Or rather, what if I wanted to index cancel first and then complete?
That's the order of the index I defined. You can reverse them if you want.
An index will be used if you just look up a prefix of it, so the above index will be used if you just specify cancel in the query, but not if you just look for complete.
|
0

To add an index over more than one column (aka multi-column index or composite index), use this syntax:

ALTER TABLE your_table
ADD INDEX (cancel, complete);

However, you might want to make sure this is the reason for your statement to be underperforming. Take a look into the manual to dig deeper into this.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.