EXPLAIN EXTENDED

Aggregate concatenation functions help creating a concatenated list out of a recordset. Useful for reports, hierarchical trees, etc.

MySQL supplies GROUP_CONCAT for this purpose. SYS_CONNECT_BY PATH and FOR XML can be used in Oracle and MS SQL.

In PostgreSQL, we cannot use these tricks, but we can create our own aggregate function. And this function will also accept two more extremely useful parameters: DELIMITER and IS_DISTINCT.
Read the rest of this entry »

From Stack Overflow:

Let's say that we have we have a table with the classic manager_id recursive relationship:

Users (user_id int, manager_id int) (refers to user_id)

If you randomly select 2 rows in the table, or 2 nodes, how do you find the lowest level common ancestor? My platform is SQL Server 2005 (Transact-SQL), but any ANSI compliant SQL will also work...

Very nice question.

This may be useful to check against any kind of common ancestry in a tree structure: classes, folders, etc.
Read the rest of this entry »

Yesterday I wrote an article about selecting random rows efficiently.

But today on Stack Overflow:

Hi

I wish to attach a column to my table which will be a random number from a sequential list = to the number of rows.

So, if my table had 999 rows, then the numbers 1 to 999 would be assigned randomly and uniquely.

Now, I figured that I could add a dummy TempRandomColumn=Rand(), sort by that and add the numbers sequentially using PHP. But that means 999 MySQL statements.

Is there a way to do this using a single MySQL statement?

Thanks for any pointers.

Well, it's just that simple:

Creating tables here:

CREATE TABLE t_zen (zen_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, zen_value VARCHAR(20) DEFAULT '無', zen_order INT);
INSERT
INTO   t_zen (zen_order)
VALUES
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL)

and performing an update:

SET @r := 0;
UPDATE t_zen
SET    zen_order = (@r := @r + 1)
ORDER BY
       RAND(20030302)

ORDER BY RAND() certainly has some beauty in it.

Usual way to select random rows from a MySQL table looks like this:

SELECT  *
FROM    t_random
ORDER BY
RAND(20090301)
LIMIT 10

This creates a fake RAND() column along with the query columns, sorts on it and returns bottom 10 results.

EXPLAIN EXTENDED on this query returns quite expected Using temporary; Using filesort

This plan will use Θ(N×log(N)) time on sorting alone. If temporary will hit memory limits, the time will increase even more rapidly.
Read the rest of this entry »

From Stack Overflow:

Ok, i've been trying to solve this for about 2 hours now... Please advise:

Tables:


PROFILE [id (int), name (varchar), ...]
SKILL [id (int), id_profile (int), id_app (int), lvl (int), ...]
APP [id (int), ...]


The lvl can basically go from 0 to 3.

I'm trying to get this particular stat: "What is the percentage of apps that is covered by at least two people having a skill of 2 or higher?"

Thanks a lot

We actually need to count ratio here:

(apps that have 2 or more skills with level 2+) / (total number of apps)

This is one of not so common cases when AVG aggregate apparently comes handy.
Read the rest of this entry »