As with most things in life, doing things right with SQL Server from the very first step makes it much easier to manage in the future. In this article, we’ll look at five important things you should verify after installing SQL Server. These are in no particular order but more of a mindmap based on years of […]
Read More
The biggest challenge of explaining efficient ways of using the VARCHAR2 data type is the fact that its table storage implementation is often confused with its PL/SQL implementation. VARCHAR2 is named for the VARiable length of CHARacters, which implies dynamic allocation of storage. This mechanism is usually efficient from the perspective of persistent tables because […]
Read More
The average total cost of a data breach right now is $3.79 million, according to the 2015 Cost of Data Breach Study: Global Analysis. Cyber attacks are one of the top reasons for this exorbitant sum. Hackers attack websites to gather commercial information, consumer information, or targeted profiles. Data breach attempts need to be thwarted quickly. […]
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
In addition to creating new indexes to improve performance, you can improve database performance with additional schema optimizations. These optimizations include using specific data types and/or column types. The benefit is a smaller disk footprint producing less disk I/O and results in more index data being packed in available system memory. Data Types Several data […]
Read More
The management of indexes—how they are created and maintained—can impact the performance of SQL statements. Combining Your DDL An important management requirement when adding indexes to MySQL is the blocking nature of a DDL statement. Historically, the impact of an ALTER statement required that a new copy of the table be created. This could be […]
Read More
For companies that live and die by their databases, a one-shot backup isn’t really the perfect solution. Typically for such companies (think Yahoo! or Google), database access is a near-constant process, and database content changes continually, often on a second-by-second basis. Data replication, which involves continual data transfer between two (or more) servers to maintain a […]
Read More
Replication in MySQL is the dynamic process of synchronizing data between a primary (main) database server and one or more secondary (replica) database servers in near-real time. Using this process, it’s possible to create copies of one or more databases so that even if the primary server fails, data can still be recovered from one […]
Read More
Equally important as backing up data in a MySQL database is the ability to restore data. Most books on the subject emphasize the importance of backing up your data regularly (and rightly so), but restoring the data is an often-overlooked aspect of this process. Backed-up files are useless if they can’t be accessed. Accordingly, you should […]
Read More