CODEWITHSUNDEEP
sqlsql-server
barcaman
barcaman

Reputation: 137

Index on foreign key or on regular column?

I am confused about where should I add indexing whether on a foreign key or on a regular column in order to make the database perform better?

the task is following:

Find all of the bugs on software Debugger that pertain to the /main.html

here is my query for that

select t.ticketid, b.data
from bugs b
inner join tickets t on b.ticketid = t.ticketid
where t.title = '/main.html' and exists (
    select 1 
    from softwares s
    where s.softwareid = t.softwareid and s.url = 'http://debugger.com'
)

now, should I make it?

create nonclustered index IX_Tickets_SoftwareId
on [dbo].[Tickets] ([SoftwareId])
go
create nonclustered index IX_Bugs_TicketsId
on [dbo].[Bugs] ([TicketsId])

or

 create nonclustered index IX_tickets
 on [dbo].[tickets] ([ticketsid, title])
 go
 create nonclustered index IX_software
 on [dbo].[software] ([softwareid,url])

which one makes it more performant?

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

For this query:

select t.ticketid, b.data
from bugs b inner join
     tickets t
     on b.ticketid = t.ticketid
where t.title = '/main.html' and
      exists (select 1 
              from softwares s
              where s.softwareid = t.softwareid and
                    s.url = 'http://debugger.com'
             );

You want indexes on:

  • software(softwareid, url) -- although this is unnecessary if softwareid is the primary key
  • tickets(title, ticketid)
  • bugs(ticketid, data)

The execution plan will scan the index for the tickets table, only for matches to the title. For each row, it will lookup whether the corresponding software exists.

If it does, it will then look up the appropriate information in bugs using an index.

It is hard to think of a faster execution plan for the query. The one exception is if there are many, many rows in tickets with the same title and very, very few that have the software you are looking for.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

Index on the foreign key is always a good idea. But your question is related to a specific query.

The index can make the DML operations slower too. so as mentioned in the comment, It is a big subject and you should read about the index before creating any index.

Still, a suggestion would be: As the EXISTS clause is used, I would recommend creating the index on software.url.

Apart from that, You can use the following query which may improve the performance.

select distinct t.ticketid, b.data
from bugs b
inner join tickets t on b.ticketid = t.ticketid
inner join softwares s on s.softwareid = t.softwareid
where t.title = '/main.html' and s.url = 'http://debugger.com'

Upvotes: 1

Related Questions