Optimizing Database Structure

Optimizing Database Structure

The database structure optimization is very important because if you store large data in in small space then it is save I/O and search time. The following points should be kept in mind when design DB structure.

  • Optimizing Data Size
  • Optimizing MySQL Data Types
  • Optimizing for Many Tables
  • Internal Temporary Table Use in MySQL

Optimizing Data Size

Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to and read from disk. Smaller tables normally require less main memory while their contents are being actively processed during query execution. Any space reduction for table data also results in smaller indexes that can be processed faster.MySQL supports many different storage engines (table types) and row formats. For each table, you can decide which storage and indexing method to use.You can get better performance for a table and minimize storage space by using the techniques listed here:

  • Table Columns
  • Row Format
  • Indexes
  • Joins
  • Normalization

Optimizing MySQL Data Types

Whenever we need optimizing MySQL data type we should take care of following points

  • Optimizing for Numeric Data

    For unique IDs or other values that can be represented as either strings or numbers, prefer numeric columns to string columns.

  • Optimizing for Character and String Types

    Use binary collation order for fast comparison and sort operations.

    When comparing values from different columns, declare those columns with the same character set and collation wherever possible.

    For column values less than 8KB in size, use binary VARCHAR instead of BLOB. The GROUP BY and ORDER BY clauses can generate temporary tables, and these temporary tables can use the MEMORY storage engine if the original table does not contain any BLOB columns.

    If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table.

  • Optimizing for BLOB Types

    When storing a large blob containing textual data, consider compressing it first. Do not use this technique when the entire table is compressed by InnoDB or MyISAM.

    For a table with several columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and referencing it with a join query when needed.

Optimizing for Many Tables

Some techniques for keeping individual queries fast involve splitting data across many tables. When the number of tables runs into the thousands or even millions, the overhead of dealing with all these tables becomes a new performance consideration.

  • How MySQL Opens and Closes Tables
  • Disadvantages of Creating Many Tables in the Same Database

Internal Temporary Table Use in MySQL

In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.The server creates temporary tables under conditions such as these:

  • Evaluation of UNION statements, with some exceptions described later.
  • Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.
  • Evaluation of derived tables
  • Evaluation of common table expressions
  • Tables created for subquery or semi-join materialization
  • Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.
  • Evaluation of DISTINCT combined with ORDER BY may require a temporary table.