Determining what index to create given a query?

Given a SQL query:

SELECT * 
FROM Database..Pizza pizza
JOIN Database..Toppings toppings ON pizza.ToppingId = toppings.Id
WHERE toppings.Name LIKE '%Mushroom%' AND
toppings.GlutenFree = 0 AND
toppings.ExtraFee = 1.25 AND
pizza.Location = 'Minneapolis, MN'

How do you determine what index to write to improve the performance of the query? (Assuming every value to the right of the equal is calculated at runtime)

Is there a built in command SQL command to suggest the proper index?

To me, it gets confusing when there’s multiple JOINS that use fields from both tables.

Answer

For this query:

SELECT * 
FROM Database..Pizza p JOIN
     Database..Toppings t
     ON p.ToppingId = t.Id
WHERE t.Name LIKE '%Mushroom%' AND
      t.GlutenFree = 0 AND
      t.ExtraFee = 1.25 AND
      p.Location = 'Minneapolis, MN';

You basically have two options for indexes:

• Pizza(location, ToppingId) and Toppings(id)

or:

• Toppings(GlutenFree, ExtraFee, Name, id) and Pizza(ToppingId, location)

Which works better depends on how selective the different conditions are in the WHERE clause.