Skip to content

# Month: November 2008

## Useful database analysis queries with INFORMATION_SCHEMA

MySQL Analysis Indexing INFORMATION_SCHEMA SQL Syntax November 26, 2008August 26, 2009

A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.

I will present queries for:

- Checking on database engines and size
- Locating duplicate and redundant indexes
- Checking on character sets for columns and tables, looking for variances
- Checking on processes and long queries (only with MySQL 5.1) Continue reading » “Useful database analysis queries with INFORMATION_SCHEMA”

## Less known SQL syntax and functions in MySQL

MySQL MyISAM SQL Syntax November 23, 2008December 13, 2008

“Standard SQL” is something you read about. All popular databases have modified version of SQL. Each database adds its own flavor and features to the standard. MySQL is no different.

Some deviations are storage engine dependent. Others are more general. Many, such as `INSERT IGNORE`, are commonly used. Here’s a list of some MySQL deviations to SQL, which are not so well known. Continue reading » “Less known SQL syntax and functions in MySQL”

## Common wrong Data Types compilation

MySQL Data Types Normalization Schema November 18, 2008December 13, 2008

During my work with companies using MySQL, I have encountered many issues with regard to schema design, normalization and indexing. Of the most common errors are incorrect data types definition. Many times the database is designed by programmers or otherwise non-expert DBAs. Some companies do not have the time and cannot spare the effort of redesigning and refactoring their databases, and eventually face poor performance issues.

Here’s a compilation of “the right and the wrong” data types. Continue reading » “Common wrong Data Types compilation”

## Dangers of skip-grant-tables

MySQL Configuration Security November 13, 2008December 13, 2008

When MySQL’s root password is lost and must be reset, there are two popular ways to create a new password. One of the options is far too popular, in my opinion.

The preferred way of setting a root’s password is by using an init-file. The process for doing this is well explained in MySQL’s manual. Using this method requires creating a simple text file, in which the required

`GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFY BY '****' WIth GRANT OPTION;`

(or, alternatively, `SET PASSWORD ...`) statement is written.

An entry must be written to my.cnf, or supplied via command line parameters:

`init-file=/tmp/my-init-file.sql`

MySQL must then be restarted. Upon restart, and before opening any outside connections, the init-file is executed. Once MySQL is up and running, the init-file entry should be dropped. Continue reading » “Dangers of skip-grant-tables”

## Two storage engines; different plans, Part II

MySQL Execution plan Indexing InnoDB MyISAM November 7, 2008December 13, 2008

In Part I of this article, we have seen how the internal structure of the storage engine’s index can affect an execution plan. We’ve seen that some plans are inherent to the way engines are implemented.

We wish to present a second scenario in which execution plans vary for different storage engines. Again, we will consider MyISAM and InnoDB. Again, we will use the world database for testing. This time, we will see how confident the storage engines are in their index search capabilities.

Many newcomers to databases often believe that an index search is always preferable to full table scan. This is not the case. If I were to look for 10 rows in a 1,000,000 rows table, using an indexed column – I could benefit from an index search. However, if I’m looking for 200,000 rows on that table (that’s 20% of the rows) – an index search can actually be much more expensive than a full table scan. Continue reading » “Two storage engines; different plans, Part II”

## Two storage engines; different plans, Part I

MySQL Execution plan Indexing InnoDB MyISAM November 1, 2008December 13, 2008

A popping question is: “Can an execution plan change for different storage engines?”

The answer is “Yes”. I will present two such cases, where the MySQL optimizer will choose different execution plans, based on our choice of storage engine.

We will consider MyISAM and InnoDB, the two most popular engines. The two differ in many respects, and in particular, the way they implement indexes and statistics: two major players in the optimizer’s point of view. Continue reading » “Two storage engines; different plans, Part I”