# EXPLAIN EXTENDED

How to create fast database queries

My latest article on SQL in general: 5 Claims About SQL, Explained. You're welcome to read and comment on it.

## Matching whole sets

From Stack Overflow:

Given the following:

```declare @a table
(
pkid int,
value int
)

declare @b table
(
otherID int,
value int
)

insert into @a values (1, 1000)
insert into @a values (1, 1001)
insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)

insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)
```

, how do I query for all the values in `@a` that completely match up with `@b`?

`{@a.pkid = 1, @b.otherID = -1}` would not be returned (only 2 of 3 values match)

`{@a.pkid = 2, @b.otherID = -1}` would be returned (3 of 3 values match)

We need to compare each set of values from `@a` to each set of `@b` and return the `id`‘s of non-matching sets.

Two sets are considered matching when for each row from the first set there is an equal row from the second set and vise versa. That is, both the values and the count of the rows should match in both sets.

First thing that comes to mind is to calculate `COUNT(*)`‘s of both sets, then calculate the `COUNT(*)` for the result of both sets joined on `value` and make sure all three are equal.

But we don’t actually need to `COUNT(*)` all values. We just need to find out the first row from either of the subsets that has no match in another subset. As soon as we find such a row, we can tell that the sets don’t match; if we don’t find one, then the sets do match.
Read the rest of this entry »

Written by Quassnoi

March 31st, 2009 at 11:00 pm

Posted in SQL Server

## Sorting lists: deleting blocks

This is article 6 of 6 on linked lists in MySQL:

Now, let’s see how can we delete a whole block of items.

The procedure that deletes a block looks like this:
Read the rest of this entry »

Written by Quassnoi

March 30th, 2009 at 11:00 pm

Posted in MySQL

## Sorting lists: moving blocks

This is article 5 of 6 on linked lists in MySQL:

Now, let’s discuss the mass updates.

In some user interfaces it is needed sometimes to select a whole block of items and move it towards the top or the bottom of the list all at once.

This may be useful to manage MP3 playlists, photo stacks, etc.

With linked lists, it’s quite simple. Just like in case of moving a single item, we need to update only three rows.

Moving the items in block, though, requires some more checking:
Read the rest of this entry »

Written by Quassnoi

March 29th, 2009 at 11:00 pm

Posted in MySQL

## Sorting lists: deleting items

This is article 4 of 6 on linked lists in MySQL:

In this aticle I’ll describe deleting an item from a linked list.

This operation is probably the most simple, as no constraints are violated here.

I’ll cover it, first, just to be complete and, second, to illustrate some tricks I used before in more detail.
Read the rest of this entry »

Written by Quassnoi

March 28th, 2009 at 11:00 pm

Posted in MySQL

This is article 3 of 6 on linked lists in MySQL:

This is quite a simple task, but has some issues we will need to handle.

When we insert a new item `A` after an existing item `B` in the linked list, we need to do the following:

1. Insert the new item and set its `parent` to `B`
2. Update the `B`‘s child’s `parent` to `A`

This seems OK, but what if we want the `id` of `A` to be autogenerated?
Read the rest of this entry »

Written by Quassnoi

March 27th, 2009 at 11:00 pm

Posted in MySQL

## Sorting lists: moving items

This is article 2 of 6 on linked lists in MySQL:

Today, I’ll expain how to move items in these lists.

To move an item in the linked list we need to relink it. If we move item `A` after item `B`, we need to update at most three rows:

1. `A`‘s `parent` is updated to `B`
2. `A`‘s child’s `parent` is updated to `A`‘s `parent`
3. `B`‘s child `parent` is updated to `A`

`B` here may be a real row or a surrogate `id` of `0` which we use to designate the first row’s `parent`.

Moving `A` after `0` means moving `A` to the top of the list.

Unfortunately we cannot rely on a single statement to perform these updates, because we have a `UNIQUE INDEX` on `parent`.
Read the rest of this entry »

Written by Quassnoi

March 26th, 2009 at 11:00 pm

Posted in MySQL

## Sorting lists

This is article 1 of 6 on linked lists in MySQL:

From Stack Overflow:

I have an app which has tasks in it and you can reorder them.

Now I was wondering how to best store them. Should I have a column for the order number and recalculate all of them everytime I change one?

Please tell me a version which doesn’t require me to update all order numbers since that is very time consuming (from the execution’s point of view).

It’s probably better to keep it in a linked list:
Read the rest of this entry »

Written by Quassnoi

March 25th, 2009 at 11:00 pm

Posted in MySQL

## Article-aware title filtering: internationalization

In the previous article, I described a query that searches for a phrase beginning with a certain letter or string, ignoring a leading article if any. This is useful in searching for movie titles.

Today, I’ll add some more features to this query.

Let’s pretend we are running an internationalized website which contains movie titles in many languages. We have a table showing us whether a certain word in the beginning of a title is an article, particle or any other part of speech that should be omitted when filtering:
Read the rest of this entry »

Written by Quassnoi

March 24th, 2009 at 11:00 pm

Posted in MySQL

## Article-aware title filtering

From Stack Overflow:

I need to make an alphabetical listing of movie titles, so I need to show only items that begin with a chosen letter. To make this slightly more complicated, many titles start with “the” or “a”, which needs to be ignored.

How would the mysql query look to achieve such a task?

Let’s create sample tables and see:
Read the rest of this entry »

Written by Quassnoi

March 23rd, 2009 at 11:00 pm

Posted in MySQL