Book Image

MySQL 8 Cookbook

By : Karthik Appigatla
Book Image

MySQL 8 Cookbook

By: Karthik Appigatla

Overview of this book

MySQL is one of the most popular and widely used relational databases in the World today. The recently released MySQL 8 version promises to be better and more efficient than ever before. This book contains everything you need to know to be the go-to person in your organization when it comes to MySQL. Starting with a quick installation and configuration of your MySQL instance, the book quickly jumps into the querying aspects of MySQL. It shows you the newest improvements in MySQL 8 and gives you hands-on experience in managing high-transaction and real-time datasets. If you've already worked with MySQL before and are looking to migrate your application to MySQL 8, this book will also show you how to do that. The book also contains recipes on efficient MySQL administration, with tips on effective user management, data recovery, security, database monitoring, performance tuning, troubleshooting, and more. With quick solutions to common and not-so-common problems you might encounter while working with MySQL 8, the book contains practical tips and tricks to give you the edge over others in designing, developing, and administering your database effectively.
Table of Contents (20 chapters)
Title Page
Dedication
Packt Upsell
Contributors
Preface
Index

Optimizing datatypes


You should define tables such that they occupy minimum space on disk while accommodating all possible values.

If the size is smaller:

  • Less data is written to or read from the disk, which makes queries faster.
  • The contents on the disk are loaded to the main memory while processing queries. So, smaller tables occupy less space in the main memory.
  • Less space is occupied by indexes.

How to do it...

  1. If you want to store an employee number, for which the maximum possible value is 500,000, the optimum datatype is MEDIUMINT UNSIGNED (which occupies 3 bytes). If you are storing it as INT, which occupies 4 bytes, you are wasting a byte for each row.
  2. If you want to store the first name, for which the length is varying and the maximum possible value is 20, it is optimal to declare it as varchar(20). If you are storing it as char(20), and just a few names are 20 characters long while the remaining are less than 10 characters long, you are wasting space of 10 characters.
  3. While declaring varchar...