EXPLAIN EXTENDED

It's very simple to delete, say, 10 top rows in MySQL:

DELETE
FROM   t_deleter
ORDER BY
          id
LIMIT 10

What if we want to delete all rows except the first 10?
Read the rest of this entry »

It is widely known that MySQL lacks support for FULL OUTER JOIN.

A common solution to work around this is using a UNION ALL to union two result sets from a LEFT JOIN and a RIGHT JOIN of two tables, adding join_column IS NULL condition to the latter.

Though it works, it's quite inefficient on large tables when used with ORDER BY … LIMIT queries, as it uses a filesort.

Let's create two sample tables and OUTER JOIN them together.

The tables are quite simple: each of them contains a million of multiples of 13 and 17, respectively, thus making each of LEFT JOIN, RIGHT JOIN and INNER JOIN of these tables non-empty:
Read the rest of this entry »

Many webmasters and network service providers keep a database of IP addresses that are not allowed to access their resources.

Such a database can be efficiently queried using MySQL's spatial abilities.

Let's create and fill the sample tables:
Read the rest of this entry »

Imagine that we are developing an email server that keeps the outgoing messages in a database.

The server is always busy sending the messages if the outgoing queue is not empty. It may have up to 10 connections at once.

If the message is failed to be sent, it's reinserted to the end of the queue until the retry count is exceeded.

As soon as all messages from a batch succeed or fail to be sent, next 10 messages from the queue are fetched.

How do we write a query to fetch a batch of messages from the queue?

This all can be done in a single query run in a loop. No range checking, no reinserting.
Read the rest of this entry »

In the previous article I wrote about ranged conditions (<, >, <=, >= or BETWEEN) on one field combined with ORDER BY another field.

We have to choose an index either for filtering or for sorting, as a single index cannot be used for both purposes.

Well, actually, it can.

There is a special access method called SKIP SCAN, designed just for these cases. This method can be used when a filtering condition applied to a secondary column of a composite index. Like, we have and index on (orderer, ranger) and search for ranger < 10.

This method scans the index bottom to top, selecting each possible orderer values. For each orderer, it applies the filtering condition on ranger, thus obtaining a valid range condition on the index that can be iterated. As soon and this range is iterated, the method defines the range for the next orderer, skipping all other values (hence the name).

SKIP SCAN does not perform as well as a simple index scan, but a query can benefit from it is there are few orderer's in the table.

Oracle supports this method directly, but MySQL, of course, not. SKIP SCAN, though, can be easily emulated.
Read the rest of this entry »

From Stack Overflow:

I have a table with roughly 100,000 blog postings, linked to a table with 50 feeds via an 1:n relationship.

When I query both tables with a SELECT statement, ordered by a datetime field of the postings table, MySQL always uses filesort, resulting in very slow query times (more than 1 second).

SELECT
`postings`.`id`,
UNIX_TIMESTAMP(postings.post_date) as post_date,
`postings`.`link`,
`postings`.`title`,
`postings`.`author`,
`postings`.`excerpt`,
`postings`.`long_excerpt`,
`feeds`.`title` AS feed_title,
`feeds`.`website` AS feed_website
FROM
`postings`
JOIN
`feeds`
ON
`feeds`.`id` = `postings`.`feed_id`
WHERE
`feeds`.`type` = 1 AND
`postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

This is a nice question that illustrates how to choose which indexes to create.

At first blush it seems to be a job for a composite index on (is_active, user_offtopic_count, post_date). Really, we could use the first two fields to filter on and the third field to order by.

But the problem here is that the query uses a range condition on postings.user_offtopic_count. In this case, the index cannot be used for ordering.
Read the rest of this entry »

This is article 6 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

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 »

This is article 5 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

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 »

This is article 4 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

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 »

This is article 3 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

In this article I'll cover inserting items into the linked list.

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 »