Attaching the EXPLAIN keyword to the beginning of a SELECT query tells MySQL to return a chart describing how this query will be processed. Included within this chart is information on which tables the query will access and the number of rows the query is expected to return. This information comes in handy to see […]
Read More
When you run a SELECT query, MySQL “remembers” both the query and the results it returns. This is accomplished by storing the result set in a special cache (called the query cache) each time a SELECT query is executed. Then, the next time you ask the server for the same query, MySQL will retrieve the […]
Read More
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
IT is full of buzzwords. DevOps, SDN, NFV, CI, big data, SDS, hybrid Cloud: the list goes on and on. It’s hard keeping up with all of these new technologies, and choosing the ones that are relevant for you and your career development is even harder. Now, I’m not here to tell you which trend […]
Read More