MySql Multicolumn Index

I already see how to make a multicolumn index using Laravel, what I really wanted to ask is if I should in my use case.

Ok here is my use case. One of my tables will storing iBeacon information that I could be hitting fairly often, as it is being used to tell the mobile API consumers which location they are at based on the iBeacon data.

For those who do not know the iBeacon protocol consists of three parts

UUID (I'm storing this in case it ever changes but theoretically we never should)
Major SMALLINT
Minor SMALLINT

Each location that gets assigned an iBeacon will get a unique major/minor pair. Since one of the main queries on that table will be a get first where major is X and minor is Y, I'm taking it that a multi-key index could help.

I'm just confused on it would be useful as they are all unique combos.

bjones2015

10 years ago

Level 11

"MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table."

Geez Brandon, why don't you read more before you ask questions!

Please or to participate in this conversation.