CODEWITHSUNDEEP
sqlsql-serverdatabaseindexing
user2975038
user2975038

Reputation: 537

Best way to index with these repeated queries

I'm trying to experiment with indexes but I'm still new to it. Let's say I use the following queries a lot so I need to make indexes for the tables music and musicians.

    SELECT *
    FROM music
    where musician_id = @id

    SELECT *
    FROM music
    where musician_id = @id and date_of_birth > @date

    SELECT *
    FROM music
    where date_of_birth > @date1 and date_of_birth < @date2

    SELECT first_name, last_name, musician_website
    FROM musicians
    where last_name = @last_name

    SELECT customer_id, first_name, last_name,musician_website
    FROM musicians
    where last_name = @last_name and musician_website= @site

What do you think is the best solution if I want to make indexes for these queries. Is it best to make one for each column that I want (for example 1 for musician_id, one for date_of_birth etc...) or maybe take them as pairs like this: musician_id with date_of_birth ,then last_name with musician_website and then just date_of_birth for the third query (where I need them to be ordered by date). What would be the best option to do here and why?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269833

The indexes you want are:

  • music(musician_id, date_of_birth) (queries 1 and 2)
  • music(date_of_birth) (query 3)
  • musicians(last_name, web_site) (queries 4 and 5)

For the last index, you can include customer_id and first_name, if you really want.

Upvotes: 2

Related Questions