SQL

Question 1

How do you optimize an SQL query?

Answer 1

• I make sure that indexes are placed correctly;
• I avoid using SELECT *, making a project before a select, hence reducing the number of columns before select.
• I replace HAVING clause with WHERE if possible and use HAVING only on an aggregated field.
• Use more attributes in SELECT clause in order to obtain distinct values avoiding using DISTINCT where possible.

Question 2

How do you optimize a query COUNT(*)

Answer 2

You need to use multi-column indexes and create indexes that’ll directly serve the queries we have to run. So we’ll add 2 new indexes, one for each of the queries. First one would be status + created_at and the second one would be duration + created_at.

CREATE INDEX service\_event\_status\_created\_at\_index ON service\_event (status, created\_at); CREATE INDEX service\_event\_duration\_created\_at\_index ON service\_event (duration, created\_at); SELECT service\_id, COUNT(\*) FROM service\_event WHERE status = 'error' AND created\_at \>= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service\_id; -- 17 rows retrieved starting from 1 in 28ms (execution: 24ms, fetching: 4ms) SELECT service\_id, COUNT(\*) FROM service\_event WHERE duration \> 1000 AND created\_at \>= DATE(NOW()) - INTERVAL 1 WEEK GROUP BY service\_id;