A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.
Suppose that we have an alphabet with four letters: A, B, a, b. We give each letter a number: A = 0, B = 1, a = 2, b = 3. The letter A is a symbol, the number 0 is the encoding for A, and the combination of all four letters and their encodings is a character set.
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
The following features have been added to MySQL 8.0:
Data dictionary:MySQL now incorporates a transactional data dictionary that stores information about database objects
Atomic data definition statements (Atomic DDL):An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction.
Queries, in the form of SELECT statements, perform all the lookup operations in the database. Tuning these statements is a top priority, whether to achieve sub-second response times for dynamic web pages, or to chop hours off the time to generate huge overnight reports.The main considerations for optimizing queries are: