understanding of database indexes. • In general! Not speciﬁc to a particular DB • A basic understanding is usually good enough • Developers need to know how to make queries fast • The rest of the DB can be a black box.
single block in the table. • Can sometimes be more efﬁcient, if you’re returning a large % of the data. • Why? DB executes multi-block reads, optimises for fewer read operations compared to index scan SELECT * FROM clients
eq_ref - tree traversal only, unique index • ref/range - tree traversal, then leaf node traversal • index - the entire index is scanned (leaf node traversal) • full - full table scan, everything is read
• SELECT * FROM clients WHERE first_name = `Binh` AND last_name = `Nguyen` • This query will not beneﬁt from the index: • SELECT * FROM clients WHERE last_name = `Nguyen` ALTER TABLE `clients` ADD INDEX (`first_name`, `last_name`);
Connor Chan Connor Tan last_name ﬁrst_name Nguyen Huy Nguyen Trung Nguyen Tuan Tan Jonathan Tan Bob (`first_name`, `last_name`) (` SELECT * FROM clients WHERE first_name = `Binh` AND last_name = `Nguyen`