In this post we’ll describe a query that accrued significant performance advantages from using a relatively long index key.  (This posting is by Zardosht and Bradley.)

We ran across this query recently when interacting with a customer (who gave us permission to post this sanitized version of the story):

SELECT name,
       Count(e2) AS CountOfe2
 FROM (SELECT distinct name, e2
        FROM (SELECT atable.NAME AS name,
                     pd1.NAME AS e2
               FROM atable INNER JOIN atable AS pd1
               ON  (atable.id = pd1.id)
               AND (atable.off = pd1.off)
               AND (atable.len = pd1.len)) ent
 WHERE ((ent.name<>ent.e2))) outside
 GROUP BY outside.name order by CountOfe2 desc;

With a table defined as

CREATE TABLE `atable` (
   `id` varchar(25) DEFAULT NULL,
   `off` bigint(20) DEFAULT NULL,
   `len` bigint(20) DEFAULT NULL,
   `name` …

Yesterday, I (Zardosht) posted an entry introducing clustering indexes.  Here, I elaborate on three differences between a clustering index and a covering index:

1. 
   
2. Clustering indexes can create indexes that would otherwise bounce up against the limits on the maximum length and maximum number of columns in a MySQL index.
   
3. Clustering indexes simplify syntax making them easier and more intuitive to use.
   
4. Clustering indexes have smaller key sizes leading to better performance.
   

Expanding MySQL’s Limits

MySQL allows at most 16 columns in an index and at most 3072 bytes per index. For tables that have more than 16 columns or a row size of greater than 3072 bytes, one cannot create a covering index that includes all of …

Here are two basic tips for proper indexing ...Don't mess with datatypes, too often people refer to an attribute defining it as one datatype in a table and as another in different tables, this actually prevents index usage in joins (forget about FKs for this time ;)) See an example here. You could declare a function based index as a workaround, but why don't we all try to make it right?Put

It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.

I've been trying to circle back and clean up things I left for later in several chapters of High Performance MySQL, second edition. This includes a lot of material in chapter 4, Schema Optimization and Indexing. At some point I'll write more about the process of writing this book, and what we've done well and what we've learned to do better, but for right now I wanted to complete the picture of what material we have on schema, index, and query optimization. The last two chapters I've written about (Query Performance Optimization and Advanced MySQL Features) have generated lots of feed back along the lines …

I came across Sphinx today via the MySQL Performance Blog (which has some good entries you might want to check out). It is an Open Source Full Text SQL Search Engine. It can be installed as a storage engine type on MySQL, and from what I hear can beat the pants off of MySQL's built-in full text search in some cases.

From the web site:

Generally, it's a standalone search engine, meant to provide fast, size-efficient and relevant fulltext search functions to other applications. Sphinx was specially designed to integrate well with SQL databases and scripting …

MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.

To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:

ALTER TABLE news ADD FULLTEXT(headline, story);

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:

SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');

The result of this query is automatically sorted by relevancy.

MATCH

The MATCH function is used to …