CODEWITHSUNDEEP
mysqlindexingconstraints
Poma
Poma

Reputation: 8484

Unique first column in multi-column index

I have multi-column index for 2 columns. Can I make first column unique without making separate index for that?

If I understand correctly mysql can use only first column in this index for lookups, so can it use it to detect uniqueness?

Upvotes: 0

Views: 130

Answers (2)

axiac
axiac

Reputation: 72256

The short answer is "No". Because it doesn't make much sense.

Indeed, MySQL is able to use a multiple-column index for operations that use only the leftmost "n" columns from the index definition.

Let's say you have an index on columns (col1, col2). MySQL can use it to find records matching conditions on both col1 and col2, GROUP BY col1, col2 or ORDER BY col1, col2. It is important to notice that col1 and col2 needs to used in this order in the GROUP BY or ORDER BY clause. Their order doesn't matter on WHERE or ON clauses as long as both are used.

MySQL can also use the same index for WHERE or ON conditions and GROUP BY or ORDER BY clauses that contain only col1. It cannot, however, use the index if col2 appears without col1.

What happens when you have an index on columns (col1, col2) and all the rows have distinct values in column col1?

Let's assume we have a table that have distinct values in column col1 and it has an index on columns (col1, col2). When MySQL needs to find the rows that match WHERE col1 = val1 AND col2 = val2, by consulting the index it can find the row that have col1 = val1. It doesn't need to use the index to refine the list of candidate rows because there is no list: there is at most one row having col1 = val1.

Sure, most of the times MySQL will use the index to check if col2 = val2 but having col2 in this index doesn't bring more useful information to the index. The storage space it takes and the processing power it uses on table data updates are too big for the tiny contribution it adds to rows searching.

The whole purpose of having indexes on multiple columns is to help searching by shrinking the list of matching rows for a given set of values when the columns included in a multiple-column index cannot be used individually because they don't contain enough distinct values.

Technically speaking, there is no way to tell MySQL you want to have a multiple-column index on (col1, col2) that must have unique values on col1. Create an UNIQUE INDEX on col1 instead. Then think about the data you have in the table and the queries you run against it and decide if another index on col2 only isn't better than the multiple-column index on (col1, col2).

In order to decide you can create the new indexes (UNIQUE on col1, INDEX on col2), put EXPLAIN in front of the most frequent queries you run on the table and check what index will pick MySQL up for use.

You need to have enough data (thousands of rows, at least, more is better) in the table to get accurate results.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108776

You asked.

I have multi-column index for 2 columns. Can I make first column unique without making separate index for that?

The answer is no. You need a separate unique index on the first column to enforce a uniqueness constraint.

Upvotes: 0

Related Questions