CODEWITHSUNDEEP
mysqlsqldatabasedatabase-optimization
Vicky
Vicky

Reputation: 1615

What index should be created?

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?

Upvotes: 0

Views: 58

Answers (1)

Tudor Constantin
Tudor Constantin

Reputation: 26861

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

Upvotes: 3

Related Questions