Indexing well on multi-criteria queries

I have a has_many, :through relationship in my Rails database, and the :through table has these important fields:

Assignment.rb (subset)
company_id
project_id
importance
order

All these columns are used heavily by queries throughout the system. Depending on the situation, these querie开发者_StackOverflows look for up to 4 of these fields and in different combinations.

I have these indexes:

company_id
project_id

Do I need to add a new index for each specific combination I use leading to potentially lots of indexes, or for maximum performance can I just add:

company_id, project_id, importance, order

and the system will just search for whichever combination it is given?

Assuming you are using MySQL, if you create an index on (company_id, project_id, importance, order) that means you can leverage that index for queries using:

• company_id
• company_id and project_id
• company_id and project_id and importance
• company_id and project_id and importance and order

But you can't index project_id or importance or order by itself. Hopefully this should help you with creating indexes. If you have a dependency, then create an index for it. For example, if you always search order when importance is involved, create an (importance, order) index. Keep in mind that indexes will be leveraged when used individually, so if you have the following indexes: (company_id), (project_id), (importance), (order) you'll be able to leverage them when searching by company_id and project_id for example.