For all those in New York this is an upcoming MySQL presentation held in conjunction with our colleagues at General Assembly on March 22nd 2011.

This presentation “How Better Indexes Save You Money” will be discussing how one simple technique can result in huge MySQL performance improvements and with zero code changes necessary. Many people think they know indexes, however MySQL and MySQL Storage Engines have some specifics that differ from more traditional RDBMS products. Learn some of the key analysis and verification techniques and be able to see immediate potential results in performance.

You can find more details at Meetup.com EffectiveMySQL. This new group is all about highly technical MySQL related content “no fluff, just stuff”.

Some people don't probably know, but there is a difference between how indexes work in MyISAM and how they work in InnoDB, particularly when talking from the point of view of performance enhancement. Now since, InnoDB is starting to be widely used, it is important we understand how indexing works in InnoDB. Hence, the reason for this post!

The more I go through others SQL, there are some common mistakes that I see developers making over and over again, so I thought why not start a series of tips that can help developers optimize their queries and avoid common pitfalls. So this post is a part of that series of tips, and this is the first tip "Avoid using a wild card character at the start of a LIKE pattern".

Relational Database Index Design and the Optimizers

Relational Database Index Design and the Optimizers. By Tapio Lahdenmaki and Mike Leach, Wiley 2005. (Here’s a link to the publisher’s site).

I picked this book up on the advice of an Oracle expert, and after one of my colleagues had read it and mentioned it to me. The focus is on how to design indexes that will produce the best performance for various types of queries. It goes into quite a bit of detail on how databases execute specific types of queries, including sort-merge joins and multiple index access, and develops a generic cost model that can be used to produce a quick upper-bound estimate (QUBE) for the …

The problem: Many a times people find that they create index but the query is still slow or the index is not being used by MySQL to fetch the result-set. Mostly the reason is that the index is not created properly, either not the right columns being indexed or the order of columns in the index does not match how its being used in the query. The order of index! What’s that. Well that’s what we will be discussing today. How does the order of column in the index matter? The order of columns in the index matters a lot,...

The slides for my presentation at FOSDEM 2010 are now available online at slideshare. In this presentation I describe a successful client implementation with the result of 10x performance improvements. My presentation covers monitoring, reviewing and analyzing SQL, the art of indexes, improving SQL, storage engines and caching.

The end result was a page load improvement from 700+ms load time to a a consistent 60ms.

10x Performance Improvements – A Case Study View more presentations from Ronald Bradford.

Here's a quick list of common mistakes that I've seen regarding indexing in MySQL.

1. Not using an index. Enable your slow query log (and consider setting long_query_time and log_queries_not_using_indexes too) and watch for queries that aren't using an index.

2. Using CREATE INDEX. While CREATE INDEX is not inherently bad, it only allows you to do one thing: add a single index to a table. It is mapped to an ALTER TABLE, so you might as well just use ALTER TABLE and then you have the benefit of being able to do multiple things in the same statement (e.g., add an index and remove an index or add 2 indexes).

3. Misusing a composite index. Take this example:
CREATE TABLE customer (
custid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(30),
lname VARCHAR(30),
INDEX (lname, fname)
);

The composite index on (lname, fname) can be used for searches …

After reading Fernando Ipar’s interesting post on partial indexes for string columns, there were two things I wanted to note:

First, this trick works quite well, but only if your like clauses only ever use the wildcard on the right hand side (or not at all). MySQL will not be able to use the index if the like contains a wildcard on the left.

Consider the following table definition:

mysql> show create table people\G
*************************** 1. row ***************************
Table: people
Create Table: CREATE TABLE `people` (
`person_id` int(15) NOT NULL default '0',
`username` varchar(255) default NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`person_id`),
KEY `people_username` (`username`(5))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row …

This time, I’m talking about indexes for string typed columns. In particular, I’ll show a procedure I find useful while looking for good index length values for these columns. I’ll use a sample table called people. Here’s what it looks like: mysql> desc people; +————+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default … Continue reading Indexing text columns in MySQL →

Related posts:

1. Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...
2. Making use of …

It is important to know and understand that while indexing columns in MySQL will generally improve performance, using the appropriate type of index can make a greater impact on performance.

There are four general index types to consider when creating an appropriate index to optimize SQL queries.

• Column Index
• Concatenated Index
• Covering Index
• Partial Index

For the purpose of this discussion I am excluding other specialized index types such as fulltext, spatial and hash in memory engine.

Example Table

For the following examples, I will use this test table structure.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  external_id INT UNSIGNED NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
) …