When optimizing queries, you don’t need to be reminded that indexes play an important role.

Something to remember though: in real-world examples a multi-column index often outperforms multiple indexes.
So it can be worth changing something like:

INDEX `index1`(`column1`),
INDEX `index2`(`column2`)

to

INDEX `index1`(`column1`, `column2`)

MySQL 5.0 introduced an internal method called “Index Merge” which merges multiple indexes when querying a table (if applicable) but I haven’t seen this being used that often. On the contrarry, MySQL often picks one of the indexes and in these scenario’s it is especcialy usefull to have one multi-column index.

As always: use EXPLAIN to see what can be improved.

After upgrading your WampServer with the latest phpMyAdmin of the 3 branch, you may not be able to sign in and instead get an “Access denied” message while everything was running smooth with phpMyAdmin 2.

This is caused by a new security feature in phpMyAdmin 3 which by default does not allow access as root without a password for the ‘config’ authentication type.

I do not have to explain why this was introduced and noone will state it isn’t a good addition. However, if you are developing on localhost and using the default WampServer set-up, you will not have a MySQL root password set. It has the advantage each of you local projects only require a database name.

If you want to circumvent this while not setting a root password, instead set the ‘AllowNoPasswordRoot‘ setting to true in your config.inc.php:

$cfg['Servers'][$i]['AllowNoPasswordRoot'] = true;

Update: since version 3.2.0 (?) a second setting is required:

$cfg['Servers'][$i]['AllowNoPassword'] = true;

Since every developer has probably struggled with data encoding once in their career, a short summary “how to get your data in and out of a MySQL database with PHP”:

• When you create your MySQL database, set the collation to one of the UTF-8 options (e.g.: utf8_general_ci). If you have no rights to create or change your database, do this on the table level.
  Note: if you use phpMyAdmin, it’s good to set your “session” encoding to UTF-8 before you sign in (although I believe this is now the default). Otherwise perfectly good data could look weird.
  
• Secondly, when you create a database connection in PHP, be sure to set the character set of your connection to UTF-8 by executing
  

  SET NAMES 'utf8'

  (notice the missing dash). Some extensions/adapters may have a method to do this (e.g. PDO) or allow you to pass it as an option.

• Third, save your PHP script/file as UTF-8 no-BOM (no byte-order mark since UTF-8 does not have byte order issues).
• Last, but most important & applicable to many more projects: set the charset of your page through the HTTP header. e.g.:
  

  header('Content-Type: text/html;charset=utf-8');

  Note: do not use the <meta> tag (solely) for this.  Only using the meta tag causes most browsers to start re-parsing your content once they notice it’s UTF-8 instead of their default assumption. This only slows down page loading.

This post is certainly not meant to be a Unicode manual but it can be a quick reference when you every wonder why you see those strange characters in your database.

Since a large part of the world doesn’t care about i18n and L10n, remarkably little articles on best practices are available about these topics.

If you live in a multilingual country however, you can’t build an application without. For a recent project I was looking at a way to avoid language specific tables (it goes without saying language specific fields are not an option either!).

Since we all like to keep our data as clean as possible, I have created my language specific tables like this example:

CREATE TABLE IF NOT EXISTS `country` (
  `code` char(2) NOT NULL,
  `language` char(2) NOT NULL default 'en',
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`code`,`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A sample country table with the ISO code as identifier, a language ISO code and the country name in that specific language. Notice that code and language form the composed primary key. In a multi-table set-up you would probably have a country table and a country_language table. Although this solution looks so much cleaner it creates a challenge when it comes to querying.

Imagine you would have these records:

DE,en,Germany
DE,de,Deutschland
DE,fr,Allemagne
US,en,United States
US,fr,Étas-Unis

and you want to get a list of countries for a German audience.

Since much data in these kinds of tables won’t be translated into all possible languages (which is the case for German in this example), you will probably want to settle for a default language if German is not available: let’s say English in this case.

Basically you want to have a result with Deutschland and United States. After some thought, this query is my best bet:

SELECT IFNULL(language_set.`code`, default_set.`code`) AS 'code',
  IFNULL(language_set.`language`, default_set.`language`) AS 'language',
  IFNULL(language_set.`name`, default_set.`name`) AS 'name'
FROM `country` AS default_set
LEFT OUTER JOIN `country` AS language_set
  ON default_set.`code` = language_set.`code`
  AND language_set.`language` = 'de'
WHERE default_set.`language` = 'en'

This creates a set of German data joined by English data if German is not available. Since MySQL supports sub-queries for some time, you can use this set as a sub-query in any larger query. In my project I’ve put this default query in each language-based model for re-use in larger queries. For example:

$sql = "SELECT DISTINCT country.*
  FROM (" . $completeSet . ") AS 'country'
  INNER JOIN other_table ON country.`code` = other_table.`country_code`
  ORDER BY country.`name`";

This would query all countries that have records in other_table.

Until now, I haven’t found a situation where this could not be used. I don’t guarantee that there isn’t one though. Any improvements are highly appreciated!

I have mixed feelings when it comes to stored procedures. On one side, they pull away a part of your applications logic which decreases maintainability. While on the other side they tend to perform better and are easily accessible (for instance, by a DBA).

Anyway, there are certainly situations were I use them and while doing so on MySQL I bumped into this bug report (and this related one). One stored procedure call did not produce any errors but when I added a second, PDO threw “General error: 2014 Cannot execute queries while other unbuffered queries are active.”.

Apparently using PDO to call multiple MySQL stored procedures on a Windows environment (e.g. your development WampServer) causes a few errors (different ones depending on the amount of calls if I’m correct). After 2 years it should have been fixed mid September ’08 so hopefully the next PHP release will correct this.

In case you ever wondered: this seems to be a reason why the popular e-commerce solution Magento currently requires a linux server.

I just searched for a MySQL clone of the ucfirst(string) PHP function which converts the first letter of a string to a capital, but couldn’t find one.
Just to save you some work – I used this:

UPDATE `table` SET
`field` = CONCAT(UPPER(LEFT(`field`, 1)), SUBSTRING(`field`, 2))

and if you want to have all other characters lowercased:

UPDATE `table` SET
`field` = CONCAT(UPPER(LEFT(`field`, 1)), LOWER(SUBSTRING(`field`, 2)))