Book Store Indexes

--Book Store Indexes.md

Book Store Indexes

We are running an online bookstore and need to keep track of what books we offer. We’ll be working with a database of three tables. The books table is created from the top selling books of all time. The customers and orders tables are randomly generated.

As a note, this project will most likely run slower for you than most other projects in Codecademy. In order to see the benefits of an index, you need a large database. We tried to make the database as small as possible but still large enough to get a benefit of creating good indexes.

script.sql

/* 1 */
SELECT * FROM customers LIMIT 10;
SELECT * FROM orders LIMIT 10;
SELECT * FROM books  LIMIT 10;

/* 2 */
SELECT * FROM pg_indexes WHERE tablename = 'customers';
SELECT * FROM pg_indexes WHERE tablename = 'orders';
SELECT * FROM pg_indexes WHERE tablename = 'books';

/* 3 */
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_book_id ON orders (book_id);

/* 4 */
EXPLAIN ANALYZE SELECT original_language, title, sales_in_millions 
FROM books 
WHERE original_language = 'French';

/* 5 */
SELECT pg_size_pretty (pg_total_relation_size('books'));

/* 6 */
CREATE INDEX idx_books_language_title_sales 
ON books (original_language, title, sales_in_millions);

/* 7 */
/* 
Add "EXPLAIN ANALYZE" in front of the query to analyze and compare the runtime. 
*/

/* 8 */
DROP INDEX idx_books_language_title_sales;

/* 10 */
DROP INDEX IF EXISTS idx_orders_customer_id;
DROP INDEX IF EXISTS idx_orders_book_id;

/* 9 */
SELECT NOW();
 
\COPY orders FROM 'orders_add.txt' DELIMITER ',' CSV HEADER;
 
SELECT NOW();

/* 10 */
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_book_id ON orders (book_id);

/* 11 */
CREATE INDEX idx_customers_first_name_email ON customers (first_name, email_address);