The database link syntax for remote updates is the same as that for remote queries. Append the name of the database link to the name of the table being updated. For example, to change the RATING values for books in a remote BOOKSHELF table, you would execute the UPDATE command shown in the following listing: […]
Read More
When it is not possible to eliminate an SQL statement to improve performance, it might be possible to simplify the statement. Consider the following questions: Are all columns required? Can a table join be removed? Is a join or WHERE restriction necessary for additional SQL statements in a given function? Column Improvement An important requirement […]
Read More
When it’s not possible to remove SQL statements that are unnecessary and the rate of change of common data is relatively low, caching SQL results can provide a significant performance boost to your application and enable additional scalability of your database server. MySQL Caching The MySQL query cache can provide a boost in performance for […]
Read More
Eliminating overhead that adds unnecessary load to database servers when SQL statements are unnecessary can improve MySQL performance, including removing duplicate, repeating or unnecessary statements. Removing Duplicate SQL Statements Capture of all SQL statements for a given function or process will highlight any duplicate SQL statements that are executed to complete a specific request. The […]
Read More
Adding indexes can provide significant performance benefits. However, the most effective SQL optimization for a relational database is to eliminate the need to execute the SQL statement completely. For a highly tuned application, the greatest amount of time for the total execution of the statement is the network overhead. Removing SQL statements can reduce the […]
Read More
If you want your server to perform optimally the best solution is to get tons of memory and big, fast drives. However, in most situations, these brute-force techniques won’t be an option. Given that we operate under less-than-ideal conditions, getting a handle on some subtler techniques for optimizing server performance makes more sense. Accordingly, this […]
Read More
In the context of keeping your queries lean and mean, you need to consider several things in terms of the table design. First, if a frequently queried table also gets a lot of changes, the way to improve performance is to stay with fixed-length fields, rather than variable-length ones. The trade-off is this: By definition, […]
Read More
In Part 1 of this article series on optimizing transactional performance of MySQL databases, we explored the importance of keeping transactions small, and in part 2, we discussed selecting appropriate isolation levels. No discussion of transactional performance is complete without a brief look at deadlocks. If you’re familiar with OS programming, you might already know […]
Read More
In Part 1 of this article, we looked at the importance of using small transactions in a transactional MySQL database. As you move from the carefree READ UNCOMMITTED isolation level to the more secure SERIALIZABLE level, the performance of the RDBMS is affected as well. The reason for this is fairly simple: The greater the […]
Read More
Because a database that supports transactions has to work a lot harder than a nontransactional database at keeping different user sessions isolated from each other, it’s natural for this to be reflected in the system’s performance. Compliance with the other ACID rules, specifically the ones related to maintaining the integrity of the database in 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
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