DBA – Solution to exercise 3

Today we solve the exercise 3 by introducing a select statement and sharing our analyse with some arguments.

I assume that you already loaded the context data in order to try the query. 

The solution

Let’s say that we only know about the name of the discipline to compute the query. This way we need to use a join on the discipline table.

We won’t discuss much about the table itself as aliases could have been taken into consideration, for now we will count on the full discipline name.

SELECT student.name 
FROM student 
INNER JOIN score ON score.student_id = student.id 
INNER JOIN discipline ON discipline.id = score.discipline_id 
WHERE discipline.name = 'Information Technology' 
ORDER BY score DESC 
FETCH FIRST 3 ROWS;

Analyse

The SQL query is straightforward, we use INNER JOINs to intersect the data between the tables based on foreign keys constraints (student id & discipline id).

For performance purpose we use a matching operator (=) instead of the LIKE or ILIKE. See here for more information about the LIKE operator. Yes we know the discipline full name :).

As we want the 3 top students, we simply order by score from the best one to the lowest and we limit the result to 3 rows.

Fetch first

Limiting result could have been achieved using the limit clause (with Postgresql server…) but using the SQL standard clause is more adapted here. 

More on performance

Have you been mistaken adding an index for the discipline name (as we search using this field) ?

Not using an index

Without any change on the table structures, if you analyse the performance, you will notice that the query takes time on the sorting process (order by).

Following is the result of the Postgresql analyse : 

After Index creation

When you add an index on the score field within its score table, the query performs much better ! 

CREATE INDEX idx_score 
ON score(score);

See now the result of the explain analyse (on postgres) :

I let you read through the documentation provided by Luke about the index on order by clause.

I hope that you learnt something today in this exercise and see you at the next one !