Assume the relation Sailors( sid : integer, sname: string, rating: integer, age:
ID: 3547186 • Letter: A
Question
Assume the relation Sailors(sid: integer, sname: string, rating: integer, age: real), where attribute sid is the primary key of the relation. There is a hash index on sid and a B+-tree index on age.
Explain how would you use the indexes to enforce the constraint that sid is the primary key of Sailors when Sailors tuples are inserted, deleted, or updated.
Give an example of an update that is definitely speeded up because of the available indexes. (An English description is sufficient.)
Give an example of an update that is definitely slowed down because of the available indexes. (An English description is sufficient.)
Give an example of an update that is neither speeded up nor slowed down by the available indexes. (An English description is sufficient.)
Explanation / Answer
1. As sid is the primary key by default the sid will be incremented - ex: INSERT OR REPLACE INTO sailors values(1,'Harsha',5,23); - this will INSERT if the the primary key sid 1 is not there in the database else it will UPDATE.
if you want to delete it use - DELETE FROM Sailors WHERE sid = 1;
2,3&4. Index will be used because you will have to use the WHERE statement to update a single row. Any time you do a search with WHERE it uses indexes. MySQL has to find the row, then update it.
It depends on what your update query is. Presumably it is like:
update Sailors
set sname = <something>
where sid = <something else>
If so, an index on id will definitely help speed things up, because you are looking for one record.
If your query looks like:
update Sailors
set rating = 1.001 * val;
An index will neither help, nor hurt. The entire table will need to be scanned and the index does not get involved.
If your query is like:
update Sailors
set sid = id+1;
Update Sailors set age=100 where sid = 123;
Then yes an index on sid will definitely increase the speed since it will find the row to update much quicker.
But for improvement, if you have columns (sid,rating,age) and rating is unique and sid is just an auto incremented column get rid of the sid column and make rating the primary key. Primary keys do not have to be auto incremented integer columns.