MySQL Indexes

For sometime now we have been looking at MySQL database. Today we will continue to dig deep in MySQL database, but we will be discussing MySQL Indexes. Indexes allows a MySQL database to be searched more quickly and faster. Although a MySQL database can still be searched without an index but as the database begin to grow large there will be a need for an index which will be used to identify each rows in a table and hence makes searching the table a very smooth and fast one. Types Of Indexes INDEX PRIMARY KEY FULLTEXT Creating an Index The...

MYSQL - The future of ALTER IGNORE TABLE syntax

"IGNORE is a MySQL extension to standard SQL. It controls how ALTERTABLE works if there are duplicates on unique keys in the new tableor if warnings occur when strict mode is enabled. If IGNORE is notspecified, the copy is aborted and rolled back if duplicate-keyerrors occur. If IGNORE is specified, only the first row is used ofrows with duplicates on a unique key. The other conflicting rows aredeleted. Incorrect values are truncated to the closest matchingacceptable value." This creates several issues for the MySQL server team: IGNORE could...

Switching off referential integrity in MySQL

If you are loading very large quantities of data into MySQL, it sometimes makes sense to switch off foreign key constraints to enable the data to load faster. There are two commands for doing this:mysql> SET foreign_key_checks = 0;That will switch off foreign key checks for the current MySQL session, and:mysql> SET GLOBAL foreign_key_checks = 0;That will switch off foreign key checks at a MySQL server level.Finally, to check the current setting of foreign_key_checks use this command:mysql> SHOW Variables WHERE Variable_name='foreign_key_checks';+--------------------+-------+|...

Killing all running queries on MySQL

On occasion you might need to kill all currently running queries against your MySQL server, without having to restart the service.I do a lot of work on ETL (Extract Transform Load) applications, where you may have very long-running queries hanging around locking resources required by your data loader, that you will want to kill before starting a new data load.Another scenario is where you have an application that is misbehaving by issuing many queries that are impacting other users of the MySQL server: so long as each application is connecting...

Technical screening questions for a MySQL DBA

In the past year, I have interviewed dozens of DBAs with a view to hiring a MySQL DBA. I have found that while many DBAs from other backgrounds, for example MS SQL or Oracle, claim to also have MySQL experience, a lot of them fail the screening once we get into the technical nuances of MySQL specifics. For example, here are a few typical screening questions I ask, along with the approximate answers I expect to get back: What are the two main storage engines in MySQL, and when would you use either? MyISAM and InnoDB. InnoDB is transaction-safe,...

Converting a MySQL database from latin1 to utf8

IntroductionWhen you create a new database on MySQL, the default behaviour is to create a database supporting the latin1 character set. This is fine for most use cases, however if your application needs to support natural languages that do not use the Latin alphabet (Greek, Japanese, Arabic etc.), then you will need to convert your database to use UTF-81 instead.In this tutorial, I will show you have to convert an existing database and tables from latin1 to the utf8 character set. Note that I am using MySQL 5.5.34.Creating a test databaseFirstly...

Finding the table containing a column name in MySQL

Sometimes when you are working with a legacy database schema that you are unfamiliar with, you are confronted with column names in error messages but not the table name that contains the column in question. An example of this is the 1048 error in MySQL, where some code has attempted to write a record to the database with a null value for a column that contains a NOT NULL constraint: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'ColumnName' cannot be null Great, but what table contains the column "ColumnName"? Answering that...

Adding a read-only MySQL user

Sometimes you may need to let a user have access to your MySQL database, for example for viewing data directly during testing or for running reports, but you do not want these users to update data or make schema changes. It is actually quite easy to set up a read-only account in MySQL using the GRANT statement, which is ideal for these purposes. Adding the new MySQL user Connect to your database as root, then add your new user like so: CREATE USER 'tester'@'%' IDENTIFIED BY 'password';  The % here means the user 'tester'...

Adding a composite unique key to an existing MySQL table

Introduction Composite unique keys are very useful for when you want to prevent duplicate data across a number of database fields in a table. For example, suppose we have the following sample table called "user_settings" to store users settings for our application: Field nameField type idint (PK) user_idint (FK) setting_keyvarchar setting_valuevarchar For each user of the system, we only want to have one setting_key value for each possible setting_key. Therefore a composite unique key would be nice to enforce this: if user_id and setting_key...

How to use ON DUPLICATE KEY for UPDATE

First a sample table mysql> drop database if exists ali; Query OK, 1 row affected (0.10 sec) mysql> create database ali; Query OK, 1 row affected (0.00 sec) mysql> use ali; Database changed mysql> CREATE TABLE test -> ( -> id int(11) unsigned NOT NULL AUTO_INCREMENT, -> external_id int(11), -> number smallint(5), -> value varchar(255), -> UNIQUE INDEX (external_id, number), -> PRIMARY KEY(id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> Let's insert...