CODEWITHSUNDEEP
mysqldatabaseindexing
Maurice
Maurice

Reputation: 7381

Does it make sense to make a composite index of 2 columns when one of the columns has a boolean datatype?

I have 2 columns, one is a foreign key column and the other one a boolean column. Both are used in a WHERE clause. (WHERE foreignId = ? AND approved = TRUE;).

I know that indexes for columns that don't have allot of variety in possible values it can hold (like boolean or enum with only a few choices) do not help speed up the query. But what if you include such a column/field in a composite index together with the aforementioned foreignkey column? Would the resulting composite index be unique enough? Or would i be better of just creating an index for the foreignkey column only?

Thank you

Upvotes: 2

Views: 1157

Answers (1)

Rick James
Rick James

Reputation: 142298

Short answer: Yes.

Long answer:

A single-column index on a low-cardinality column (boolean / enum / etc) is rarely useful. But a multi-column index including such is likely to be useful, sometimes very effective.

The cardinality (selectivity) of each column in a multi-column index does not matter. And it does not matter which order you put them in the index.

One example being very effective:

WHERE foreignId = ? AND approved = TRUE
ORDER BY foo
LIMIT 10

together with

INDEX(foreignId, approved,  -- in either order
      foo)

In that case, it can do all the filtering, then move onto the ORDER BY and avoid sorting. But more importantly, it can stop after 10 rows. Without that index, it would collect possibly thousands of rows, sort them, and then peel off 10.

Foreign keys... Make sure you have a composite key, whether or not you have the FK. A single-column FK should notice if you have a composite index starting with that column and not create an extra index.

More discussion in my blog: https://mysql.rjweb.org/doc.php/index_cookbook_mysql

Upvotes: 4

Related Questions