EXPLAIN EXTENDED

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

• Sorting lists
• Sorting lists: moving items
• Sorting lists: adding items
• Sorting lists: deleting items
• Sorting lists: moving blocks
• Sorting lists: deleting blocks

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 »

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

• Sorting lists
• Sorting lists: moving items
• Sorting lists: adding items
• Sorting lists: deleting items
• Sorting lists: moving blocks
• Sorting lists: deleting blocks

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 »

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 »

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 »

Sometimes we need to select all rows for a table that have duplicate values in some of the columns. Like, we want to select all user comments for all posts commented by more than one user.

If there are two or more comments for a post, we select all comments for this post; if there is only one comment, we select none.

Let's create the sample tables to illustrate our needs:
Read the rest of this entry »

Today, we will check our structure for loops.

Loops in hierarchical queries occur when a row contains itself in its ancestry chain.

In general, it's a sign of a database logic flaw and should be avioded, but sometimes it's deliberately used.

The simplest and most easily detectable case is having id equal to parent (meaning that the row is both its father and child). This may be tested by using a simple comparison condition.

But a loop of course can be more complex: a row can be its own grandfather, grand-grandfather etc.

To detect such loops, we will improve our functions a little.
Read the rest of this entry »

In this article, we will find the leaves of the hierarchy tree.

A row is a leaf in a hierarchy tree if it has no children.

In Oracle, there is a speical pseudocolumn that tests if a given row is a leaf in a CONNECT BY query. Surprisingly, it's called CONNECT_BY_ISLEAF.
Read the rest of this entry »

Today, we will improve the query a little more.

If we develop the catalog with categories, subcategories etc, it's useful to have breadcrumb links somewhere in the header of the page.

In Oracle, there is a special function called SYS_CONNECT_BY_PATH(row_expression, delimiter), that returns the ancestry chain of a given node.
Read the rest of this entry »

Today I will add some more features to these queries.

We have almost 100,000 rows in the table, 8 hierarchy levels, and our query is written so that it always selects all rows.

Now let's try to present our data in a Yahoo-style catalog. We need only top 2 levels: parent categories and their subcategories.
Read the rest of this entry »

There is no need in explaining how convenient hierarchical queries are.

A quick reminder: hierarchical data is a parent-child relationship contained in one table.

A typical task is to return values from the table in the following way:

1. Resultset should be sorted like a tree, that is lexical sort by ancestry chains
2. Depth level should be returned along with each row

It may sound confusing, but it's very simple in fact, like shown on this Oracle query:
Read the rest of this entry »