Indexes
- Indexes are used to speed up data retrieval in the database.
- The main indexes are clustered and non-clustered.
- Indexes speed up searching, but slow down data updates.
- Indexes must be placed in strategic places for the search (compounds of the tables or columns to be searched).
- Setting the primary key automatically also sets the clustered index; it is possible to have only one clustered index after the table.
- There can be multiple non-clustered indexes on the table.
- Full Text Index allows a very fast and efficient search of columns with a large amount of text.
- Full Text Index works only on text columns.
views
- The view is the object that contains the tabular structure obtained as a result of a query.
- We use views when we want to simplify the query architecture or protect certain data.
- The syntax to create the view is CREATE VIEW myView AS query or CREATE OR REPLACE VIEW myView as query.
- Data validation by view is slower than data validation by direct queries.
- When creating views that need to be updated, we must be careful about using joined tables, but we must not use grouping, aggregate functions, and query union with the UNION keyword.
- The user may have rights to the view, although he does not have rights that this view includes.
- The view is deleted with the drop view command .
Stored Procedures
- Stored procedures allow portability of business logic and reduced data flow (ie, higher speed) between the application and the database.
- The syntax for creating the stored procedure on the MySQL server is CREATE PROCEDURE my_procedure().
- The syntax for creating the user-defined storage function on the MySQL server is CREATE FUNCTION function_name() RETUNRS data_type RETURN value .
- The procedure call on the MySQL server is done with the CALL command, while the functions are called and used within the SQL query.
- Stored procedure can have input, output, or both input and output parameters, while user-defined functions can only have input parameters and a return value.
- The main difference between stored procedures and functions is that functions return only one value and can be inserted into queries.
- Conditional syntax is accessible within stored procedures: conditional blocks and loops.
Built-in functions
- In SQL, two types of functions are distinguished: scaling and aggregates.
- SQL distinguishes several types of functions: string, numeric, summary, date and control.
- Date manipulation functions allow various data logging, conversions, filtering, date calculation, etc.
- Numeric functions allow the manipulation of numerical values (extracting the square root, squaring, rounding, trigonometry, etc.).
- String functions are used to manipulate textual values (joining, cutting, deleting empty characters, counting).
- Summary functions are used to obtain information based on the state in the source (row numbers, sums of row values, maximum row values, minimum row values).
- Control functions are used to control the output information from the query.
Cursors and triggers
- Triggers are functional structures that are activated when the data in the table is updated.
- Triggers are divided according to the activation time (BEFORE and AFTER) and the command that activates them (INSERT, UPDATE and DELETE).
- The BEFORE trigger occurs before the data is updated.
- The AFTER trigger occurs after the data is updated.
- Temporary tables are available from the trigger code, where you can check the values that will be entered in the table (old – current data in the table and new – data to be entered).
- Not all trigger types create both temporary tables (INSERT creates only new, UPDATE creates both new and old, and DELETE creates only old).
- Cursors allow sequential passage through the rows of the table.
Transactions
- Transactions are ways of executing manipulation commands, which allow the execution of one command or all of them.
- Transactions work on the InnoDB storage mechanism, not on MyIsam.
- There are explicit and implicit transactions.
- The implicit transaction does not have an accurately timed start.
- The explicit transaction starts with the START TRANSACTION command.
- Transactions are completed with the ROLLBACK or COMMIT commands.
- ROLLBACK destroys all orders in the transaction.
- COMMIT confirms the transaction.
- Competition between transactions can be regulated with the isolation level of transactions.
- It is possible to influence the speed with which the SQL manipulation commands will be active.