EXPLAIN EXTENDED

Sometimes we need to get a sample row from a table satisfying a certain condition. Like, get a first row for each month.

MS SQL and Oracle supply analytical function ROW_NUMBER() for this purpose.

Let's create a simple table to illustrate our needs and see how do we query it.
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 »