Multiple indexes on multiple columns

Question

Lets say I have a decent sized MySQL table (that we'll call departments) with a bunch of columns that cluster together like so:

Departments Table:

| id | ds_settings | ds_reports | sales_settings | sales_reports | eng_settings | eng_reports | ops_settings | ops_reports | queryable_id | queryable_type |
|----|-------------|------------|----------------|---------------|--------------|-------------|--------------|-------------|--------------|----------------|

So as far as columns are concerned, we have "settings" and we have "reports". When this table is queried, it will typically be looking for just the all the settings or reports for a given "queryable" id and type.

So MOST queries to this table will end up looking something like this:

SELECT ds_settings, sales_settings, eng_settings, ops_settings
    FROM departments
    where queryable_id = 1
      AND queryable_type = "User"

Why question is, what's a the correct way to index this table? Does it make design sense to include an index that encompasses all of "settings" AND all of "reports", e.g.:

UNIQUE KEY `index_on_settings` (`queryable_id`,`queryable_type`,
                   `ds_settings`,`sales_settings`,`eng_settings`)

...or is this misunderstanding how compound indexes are supposed to work?

Index doesn't boost selected columns, based on your case, you need one combined index KEY `indxqry` ( `queryable_id` , `queryable_type` ) — Jawad Al Shaikh
– Jawad Al Shaikh, Commented Aug 14, 2018 at 0:19
@JawadAlShaikh Thanks for the response - is there any reading you might recommend that can help me better grasp the concept you're talking about? — TheNovice
– TheNovice, Commented Aug 14, 2018 at 0:20
@danblack answer provide full answer with valid reference to official docs about indexes — Jawad Al Shaikh
– Jawad Al Shaikh, Commented Aug 14, 2018 at 0:26
For that one example, have a 2-column composite index, in either order. — Rick James
– Rick James, Commented Aug 26, 2018 at 22:25

danblack · Accepted Answer · 2018-08-14 00:21:20Z

When considering a key the following elements should be used for the index in order. Fields used for:

joins
where (constant fields)
where (range fields)
sorts
group by

In this case you are searching by two fields by constant lookup value so keep those as the index. There is no need to impose a unique constraint.

While you can include fields you retrieve in an index it has the downside of increasing the size of the entries in the index and making it slower to search the index. If you had one small field there on an exceptionally common query it might be worth it however if your case it would seem premature.

So:

ALTER TABLE departments ADD KEY index_on_settings (queryable_id, queryable_type)

I'm assuming id is a primary key.

Recommend reading through https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html. There is also a good presentation here on index usage https://github.com/jynus/query-optimization.

displayName · Accepted Answer · 2018-08-14 01:10:41Z

1

Two points in response to your question:

The index is to be built on the attributes in the WHERE clause, not on the attributes in the SELECT clause.
You should build the index on bare minimum attributes needed, because if you include more attributes than needed, then your inserts and updates will have to update the indices too, causing them to slow down.

answered Aug 14, 2018 at 1:10

displayName

14.4k9 gold badges65 silver badges77 bronze badges

Collectives™ on Stack Overflow

Multiple indexes on multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related