One essential task of any database administrator is to make sure the data is protected from loss. This is accomplished by regular backup and test restorations of your database. When disaster strikes (and it will, make no mistake about that), you will be better equipped to deal with it if you perform these backup and restore […]
Read More
You might need to restore corrupted tables (or even an entire database) from your backups and use the update logs if a table gets damaged or deleted by accident. In case of relatively minor damage, however, MySQL provides several options for table repair. This next section deals with what you can do if this is […]
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
Stored routines are one of the newer additions to MySQL and, as such, are significantly less optimized than other components of the server. As a result, it’s generally not advisable for your application to rely entirely on stored routines, as doing so will likely degrade performance significantly. That said, stored routines do have a role […]
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
It’s common to see queries like these: SELECT (*) FROM airport; SELECT COUNT(*) FROM airport; These queries use the asterisk (*) wildcard for convenience. However, this convenience comes at a price: The * wildcard forces MySQL to read every field or record in the table, adding to the overall query processing time. To avoid this, explicitly name the output fields you […]
Read More
Session-based server variables can also come in handy if you want to avoid nesting queries within each other. Therefore, while the following query will list all flights where the current price is above average: SELECT FlightID FROM stats WHERE CurrPrice > (SELECT AVG(CurrPrice) FROM stats); you can accomplish the same thing by splitting the task into two queries and using a server-side MySQL […]
Read More
Although MySQL comes with built-in intelligence to automatically optimize joins and subqueries, this optimization is far from perfect. An experienced database architect can often improve query performance by orders of magnitude through simple tweaks to the way queries are written. For example, MySQL is better at optimizing joins than subqueries, so if you find the […]
Read More
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