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'
For exactly that query, create an index on
ucity and one on
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