Database Systems with SQL - Full Course - Database Systems - Physical Design Process and Storage Engines

When you specify the database tables, columns and keys, this is referred to as the logical design. But specifying the table indexes, structure, and partitions is the physical design. Logical design affects query results, while physical design never affects results but can help to optimize performance for faster queries. The physical design depends on a storage engine, also called a storage manager, which translates query processor instructions into low-level commands that are able to access data on storage media. The MySQL database has several options for storage engines: InnoDB - included when you install MySQL and is the default storage engine is used for transaction management, foreign keys, referential integrity, and locking. MyISAM - mostly used for analytic applications, and provides limited transaction management and locking capabilities. MEMORY - provides fast access to small databases that are stored in memory With the InnoDB engine for MySQL: - Indexes are B+tree indexes - Primary keys automatically get a primary index, and foreign keys automatically get a secondary index - You can manually create secondary indexes using the CREATE INDEX statement. Tables with a primary key have a sorted structure, while tables without a primary key have a heap structure To create an index: INDEX IndexName ON TableName (Column1, Column2, ..., ColumnN); To show an index: SHOW INDEX FROM TableName; To drop an index: DROP INDEX IndexName ON TableName; EXPLAIN statement If you need a result table to see how a statement (such as any select, insert, update or delete) is executed by the storage engine, you can run the command: EXPLAIN statement; Physical Design Process: Create initial design – Here you create a primary index for each primary keys and a secondary index for each foreign keys, which is done automatically for you in MySQL with the InnoDB storage engine Identify and EXPLAIN slow queries – Look at the results of the explain statement for each slow query statement or check out the query log Create/drop indexes – after looking at the EXPLAIN result table, it might be a good idea to create an index when the rows value is high and the filtered value is low. Also, you should drop or delete indexes that are never used. Partition big tables – If after creating indexes and you are still getting slow queries, then maybe you should try a partition. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!