0

I am following an online database course and I got this question.

Assume I have tables USER, CHECKIN and PLACE.

USER(uid, uname, ucity), uid is the primary key.

PLACE (pid, pname, pxcoord, pycoord, pcity), pid is the primary key.

CHECKIN (uid, pid, cdate, ctime), (uid, cdate, ctime) is the primary key.

The query is

select c.uid, c.pid c.cdate
from user u join checkin c join place p 
where ucity='NewYork' and pcity='Chicago'

Only allowed to create up to two index structures, what is the best choice and why?

Vicky
  • 1,465
  • 2
  • 12
  • 21

1 Answers1

3

For exactly that query, create an index on ucity and one on pcity.

Also, if you're allowed to create up to 2 indexes, maybe you're allowed to also modify one. If that's the case, modify the primary key on the CHECKIN table to also contain pid, right after uid, because most probably in the near future you'll transform this query from a cross join in an inner or left join

Community
  • 1
  • 1
Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
  • Could you explain why not create index on uid and pid, as they are join keys? – Vicky Apr 30 '16 at 06:20
  • 1
    They're not join keys in this query. This query is a cross join query (a cartesian product between all rows of those 3 tables) because you don't have on clauses with your JOINs – Tudor Constantin Apr 30 '16 at 06:25
  • Ok, so if it is the natural join, should we first consider index on join keys then keys in where clause? – Vicky Apr 30 '16 at 06:39
  • That's right. And, if you have to chose just one of the ucity or pcity columns to index, chose the one in the table that has more rows. – Tudor Constantin Apr 30 '16 at 06:42