Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Sunday, August 23, 2009

Examples of bad queries

In my years of teaching MySQL topics, I've noticed that many people write bad queries. It's not necessarily their fault. It could be that they don't understand some aspect of SQL, or that they solved the problem with the first technique that came to mind, or that their query is fine except that MySQL optimizes it poorly. I'll give a few examples.

Correlated vs. Non-Correlated subquery
For those that don't know what a correlated subquery is: it means that the subquery relies on values from the outer query. If the subquery could be executed by itself, then it would be non-correlated. Here's an example of a non-correlated query. Using the `world` database, I want to return all cities that have a population larger than New York's population:

SELECT name FROM City WHERE population > (SELECT population FROM City WHERE name = 'new york');

Since the subquery can be run by itself, it's non-correlated. These queries are generally okay, though if they are slow you should check to see if indexes are being used or consider re-writing as a join.

Now here's an example of a correlated subquery. The query returns the countries that have a city bigger than 9 million people:

SELECT name FROM Country AS co WHERE EXISTS (SELECT * FROM City WHERE population>9000000 AND co.code = countrycode);

Notice that the subquery references the outer query (co.code). This causes the subquery to execute for each row of the outer query. That's a lot of work! This could have been written as a non-correlated subquery:

SELECT name FROM Country WHERE code IN(SELECT countrycode FROM City WHERE population>9000000);

The problem with the above query (as you can verify with EXPLAIN) is that MySQL 5.1 executes this as a correlated subquery even though it shouldn't be. This is fixed in 6.0! In the meantime... run EXPLAIN on your slow subqueries and look for "DEPENDENT SUBQUERY" - that means it's correlated. Try to re-write it. Here's the above query as a join:

SELECT DISTINCT Country.name FROM Country JOIN City ON code = countrycode WHERE City.population > 9000000;

The MySQL optimizer in 5.x has a lot of options when optimizing joins, such as choosing the best table order and considering all indexes.

The point of this post is: the easiest query is not always the best one. If it's slow, use EXPLAIN and think about another strategy. There's more than one way to skin the cat. And remember to upgrade to the latest version of MySQL - the optimizer team is hard at work.

Friday, August 14, 2009

Common indexing mistakes

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 that use lname and fname OR for searches only on lname. For example these queries can use that index:
SELECT * FROM customer WHERE fname = 'Sarah' AND lname = 'Sproehnle';
SELECT * FROM customer WHERE lname = 'Sproehnle'; -- uses the first part of the index
SELECT * FROM customer WHERE lname LIKE 'Spr%'; -- uses the first part of the index
SELECT fname FROM customer WHERE lname = 'Sproehnle'; -- uses it as a covering index
SELECT * FROM customer WHERE lname = 'Sproehnle' ORDER BY fname; -- uses the index and avoids filesort

However, the following query will not use the index:
SELECT * FROM customer WHERE fname = 'Sarah'

Another mistake is to add an additional index on lname. In most cases you wouldn't want that since the composite index can already be used for looks on lname.

4. Using an expression on a column. I often see developers write queries that use a function or other expression in their WHERE clause. If the expression is directly on the column, an index can't be used. For example, let's say I want customers who joined this year, I could do this:
SELECT * FROM customer WHERE year(customer_join_date) = 2009; -- Cannot use an index on customer_join_date

If I rewrote the query I could take advantage of an index on customer_join_date:
SELECT * FROM customer WHERE customer_join_date BETWEEN '2009-01-01' AND '2009-12-31';

MySQL does not have function-based indexes. There are ways to mimic these, but I'll leave that for another blog post.

5. Appending the primary key to an index on an InnoDB table. This applies to InnoDB, not MyISAM. InnoDB uses the primary key as a row pointer in your secondary indexes. So effectively an index on column lname is actually implemented like an index on (lname, custid)