MySql Indexing for multiple columns

Question

My table contains columns c1,c2,c3,c4,c5,c6 , I have more than 64 combination of queries like this

my queries are

where (c1=1 and c2=2 and c3=3)
where (c1=1 and c2=2 and c3=3 and c4 =4 and c5 =5 )
where (c1=1 and c2=2 and c3=3 and c4=4)
where (c1=1 and c2=2 and c3=3 and c6)
where ( c2=2 and c3=3) ..etc

I created index for multiple columns using this query

create INDEX `allpair` ON TABLE_NAME (c1,c2,c3,c4,c5,c6);

but it's not working for all combinations Should I need to add indexes for all combinations?

keep in mind that the order is important. if your index starts with c1, any where clause not including c1 will not benefit from that index. think of it like a phone book sorted by last name, then first name. only searching for the first name won’t benefit from that order at all — Psi
– Psi, Commented Oct 6, 2022 at 5:03
You can't possibly index all combinations. You might find that your schema isn't going to work, and you may need to restructure, or create a derivative schema that's far more relational so you can work with it. I've done this before where you create separated look-up tables, something along the lines of a star schema. These are super annoying to work with in the course of regular operations, but are really fast to query. — tadman
– tadman, Commented Oct 6, 2022 at 5:22
Should I need to add indexes for all combinations? It depends. If the query speed is critical then the index for this query improvement must be created. From the practice - the amount of such really critical queries is low, 2-3, not more. — Akina
– Akina, Commented Oct 6, 2022 at 6:11

Rick James · Accepted Answer · 2022-10-07 04:17:39Z

1

There is no good answer.

The index you propose helps some with any query involving c1=constant, but does not help if that is missing. And, the farther you get into that multi-column index, the less useful the columns become. That is, your allpair is useless for your query 5.

A partial answer:

build a "few" 2- or 3-column indexes.
focus on columns that are likely to be tested =constant; this is because "range" tests do not optimize as well.
tend to start the various indexes with different first columns
look at the likely queries -- you will probably find a string bias in what columns are tested. In real estate, most queries include 'number of bedrooms' but do not include 'has gazebo'.

If you had just those 5 queries, then INDEX(c2,c3,c1) (or INDEX(c3,c2,c1)) would be perhaps best.

You cannot provide all combos. There is a hard limit of 64 indexes on a table. 5-10 is a practical limit.

Are the constants numbers? Do you need "ranges" of numbers? Are some of them strings? [If you have over-simplified the question by showing only =number, you are preventing me from providing some other solutions.]

More

Ranges (>, BETWEEN, !=, LIKE 'abc%', sometimes IN, etc) -- The index won't go past that column. So, tend to put such columns at the end of a composite index.
2 ranges in a WHERE -- The Optimizer will decide which one to use; usually it will pick the better one.
FOREIGN KEY -- This is a combination of a constraint that is used when INSERTing and INDEX that may be useful for SELECT. When adding an FK, if there is already a useful INDEX, the FK won't add a redundant index.
Strings (VARCHAR, not TEXT) -- Think of them similar to numbers when it comes to indexing.
DATE, DATETIME, TIMESTAMP (etc) -- These are, shall we say, glorified numbers. However, they are usually used as a "range".
Numbers, string, and dates all can be involved in either = or range tests. My index advice applies equally to each.

More on indexing: Index Cookbook and perhaps Entity-Attribute-Value

edited Oct 7, 2022 at 4:17

answered Oct 6, 2022 at 15:14

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

2 Comments

Abhishek P Over a year ago

Yes, values will be ranges, strings, and foreign keys also, Please explain those cases.

Rick James Over a year ago

@AbhishekP - I added some info. What I added does not invalidate what I had already said. If you would like to make a try at the indexes, I will advise further. But do use real column names.

Collectives™ on Stack Overflow

MySql Indexing for multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related