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













  [Read more...]

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 | Extra          |

+————+——————+——+—–+———+—————-+

| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |

| title      | varchar(250)     | NO

  [Read more...]

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

  [Read more...]

So, how does one gather statistics on indexes? With InnoDB it’s one thing, with MyISAM it’s another thing. This post however, will focus on MyISAM. There are various ways, each having their own drawbacks and positive sides.

What about ANALYZE TABLE? It’s great — very fast in InnoDB. But with MyISAM a lock occurs while scanning the table.

OPTIMIZE TABLE? Still relatively quick with MyISAM, unless the table in question is huge. InnoDB requires an ALTER TABLE — it might take forever, depending on the server configuration, as OPTIMIZE TABLE for InnoDB maps to a ALTER TABLE tableName ENGINE=InnoDB

We all know how ANALYZE TABLE, REPAIR TABLE, and OPTIMIZE TABLE work with MyISAM. However, there’s a less explored way, for MyISAM

  [Read more...]

I mentioned earlier that I'd blog about progress on the book as we go. It's not only progress on the book itself -- I want to write about the process of writing, because I think it's very interesting and relevant to software engineering. I'm finding a lot of the work in writing a book comes from some of the same things that make software hard: coordinating work, deciding what should go where, and so on.