A surprising number of people in online forums request information about slow queries without having tried to add an index to a frequently accessed field. As you know from Chapter 3, tables with fields that are accessed frequently can be ordered by creating an index. An index points to the place on a database where […]
Read More
Under MySQL’s current implementation, a stored function can only return a single value. However, there is a not-so-pretty workaround: create a temporary table within the function body to store the values returned, and then access this table outside the function. Here’s an example: mysql> DELIMITER // mysql> CREATE FUNCTION get_airport_names(min_terminals INT) -> RETURNS INT -> BEGIN -> DECLARE count INT DEFAULT 0; -> CREATE TEMPORARY TABLE -> IF NOT EXISTS -> get_airport_names_out (value VARCHAR(255)); -> DELETE FROM get_airport_names_out; -> INSERT INTO get_airport_names_out (value) -> SELECT AirportName FROM airport -> WHERE NumTerminals >= min_terminals; […]
Read More
As your SQL business logic becomes more complex, you might find yourself repeatedly writing blocks of SQL statements to perform the same database operation at the application level—for example, inserting a set of linked records or performing calculations on a particular result set. In these situations, it usually makes sense to turn this block of […]
Read More
MySQL fully satisfies the ACID requirements for a transaction-safe RDBMS, as follows: Atomicity is handled by storing the results of transactional statements (the modified rows) in a memory buffer and writing these results to disk and to the binary log from the buffer only once the transaction is committed. This ensures that the statements in […]
Read More
In the SQL context, a transaction consists of one or more SQL statements that operate as a single unit. Each SQL statement in such a unit is dependent on the others, and the unit as a whole is indivisible. If one statement in the unit does not complete successfully, the entire unit will be rolled […]
Read More
To speed up searches and reduce query execution time, MySQL lets you index particular fields of a table. The term “index” here means much the same as in the real world. Similar in concept to the index you find at the end of a book, an index is a list of sorted field values used […]
Read More
Primary keys serve as unique identifiers for the records in a table, while foreign keys are used to link related tables together. When designing a set of database tables, it is important to specify which fields will be used for primary and foreign keys to clarify both in-table structure and inter-table relationships. About Foreign Keys […]
Read More
Primary keys serve as unique identifiers for the records in a table, while foreign keys are used to link related tables together. When designing a set of database tables, it is important to specify which fields will be used for primary and foreign keys to clarify both in-table structure and inter-table relationships. Primary Keys You […]
Read More
MySQL supports many different storage engines for its tables, each with its own advantages and disadvantages. While all of MySQL’s storage engines are reasonably efficient, using the wrong storage engine can hinder your application from achieving its maximum possible performance. For example, using the ARCHIVE engine for a table that will see frequent reads and […]
Read More
There are several limitations to how indexes are used and managed in MySQL in comparison to other RDBMS products. Cost Based Optimizer MySQL uses a cost based optimizer to prune the possible query tree to create the most optimal SQL execution path. MySQL has limited capabilities for using generated statistics to aid the optimizer as […]
Read More
Although adding indexes can help to optimize the performance of SQL statements, the addition of indexes comes at a significant cost. DML Impact Adding indexes to a table affects the performance of writes. This can be easily shown from the artist table used in this chapter. Looking at the current definition shows a large number of […]
Read More
MySQL has a small number of query hints that can affect performance. There are hints that affect the total query and those that affect how individual table indexes are used. Total Query Hints All of the total query hints occur directly after the SELECT keyword. These options include SQL_CACHE, SQL_NO_CACHE, SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CALC_FOUND_ROWS, and […]
Read More