SQL creating both multiple column index and single column index on a Table

Question

I have a Table say Products.
Its having following columms:
ID
AdminID
CategoryID
Some queries on this table will be only on AdminID (Q1) and some only on CategoryID (Q2). However there will be few queries on both, i.e. AdminID and CategoryID (Q3).
It seems that in this case I need to create 2 indexes (excluding the one on id):

• Index on both AdminID and CategoryID with AdminID's position as 1 in index. This should handle Q1 and Q3.
• Index on only CategoryID. This should handle Q2.

Is above a good design ?

Answer 1

Short answer is - Yes, that's fine. Making indexes when needed is "good design", if the usage of the data warrants it.

Longer answer is Yes, with a few considerations;

That the amount of data in the table warrants the indexes, meaning that the overhead in maintaining the data on updates, inserts and deletes in multiple indexes versus just extracting all data from the table on each query.
Basically - if for example, the table will not contain "a lot of data", then it might be better to just avoid indexes on it all together.
Also how is the distribution? If 80% (just a number as example) of the data will have the same value in the field/column, an index might be less useful on selections, because the query optimizer will still have to basically touch most rows anyway and therefore the overhead in maintaining the index is perhaps larger than the gain from having it on select.

Also without knowing the rest of your database design (related tables), it's impossible to say whether your structure is "optimal" and whether you actually need the values in that table or they should be in another table or whether your queries possible could be changed.

Answer 2

Speaking generally (ignoring RDBMS specifications) you may consider any index on Products table being another table containing sorted data of the Products table based on index columns. That would be an efficient structure when searching the table based on the index column.

In the other hand, having index on any table will have a performance penalty on insert, update and delete operations on the table due to cost of synchronization of the index (index structure needs to be kept sorted).

AdminID and CategoryID are foreign keys, having index on each of them is recommended, the index will prevent Products table being locked when delete or update happens on Category or Admin(user) table also that will help query performance.

Having a composite index on AdminID + CategoryID will be a matter of trade off between transaction performance and query performance. That would need mentoring by analyzing the database to find good reasons.
MySql can log queries that take longer to execute than a specified threshold of time, calling it slow query log and its long_query_time parameter defaults to 10 seconds.
After nominating your slow query you need to see the reason by analyzing query execution plan (QEP) of the query, then deciding index creation or removal.

Know your data, know your queries, and MENTOR your indexes.

Answer 3

You are correct. You only need to create two indexes: (AdminID, CategoryID) and (CategoryId) since AdminID covers CategoryID, AdminID will be used when the optimizer deems it cost effective. There is no reason to create a separate AdminID index.