After reading one of the recent posts from Gobi [EMAIL PROTECTED] 
I took his successful query and modified it for one of my tables. It indeed 
produce the correct result, but in the process raised some questions.

1.         Why do list_ID and acct_ID not have to be qualified with a table 
name or alias? 

mysql> SELECT list_ID, list_Name, acct_ID, list_Qty
    -> FROM   lists
    ->        INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
    ->                    FROM   lists
    ->                    GROUP BY acct_id
    ->                    ) AS t
    ->        USING (acct_ID, list_ID)
    -> WHERE  list_Active = 'Yes'
    ->        AND cpny_ID = 'RER1'
    -> LIMIT  100,10;
+---------+----------------------------+---------+----------+
| list_ID | list_Name                  | acct_ID | list_Qty |
+---------+----------------------------+---------+----------+
|       3 | Farm                       | BA8M    |        0 |
|      10 | Woodbury                   | BA8Y    |      100 |
|       2 | Brookview Heights 03-23-04 | BA9O    |      278 |
|       4 | Magnet Mailing             | BABA    |      250 |
|       2 | Fall Back                  | BABM    |      223 |
|       1 | Contact list               | BACF    |       71 |
|       4 | Friends/Family             | BAE2    |       10 |
|       1 | St. Michael                | BAE7    |      139 |
|       2 | JS Prospects               | BAE8    |      196 |
|       1 | Home Focus                 | BAE9    |       55 |
+---------+----------------------------+---------+----------+
10 rows in set (0.03 sec)


2.         While the subselect does work, it appears to generate a cartesian 
product. Initial guess with 5.0 and stored procedures would be that 

            CREATING TEMPORARY TABLE
            INSERT max values in temporary
            SELECT from main table joined with temporary

            would run faster and still allow this to be done with 1 statement.

However,  even though the explains would indicate that this was so {23508 * 
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After doing 
some playing, it is the INSERT into temporary that adds the 
time even though the table was memory resident. Trying a similar request on a 
table with 3.5M rows still favors the subselect 
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}. 

Has EXPLAIN just not caught up with SUBSELECT logic or is there something else 
going on?



mysql> EXPLAIN
    -> SELECT list_ID, list_Name, acct_ID, list_Qty
    -> FROM   lists
    ->        INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
    ->                    FROM   lists
    ->                    GROUP BY acct_id
    ->                    ) AS t
    ->        USING (acct_ID, list_ID)
    -> WHERE  list_Active = 'Yes'
    ->        AND cpny_ID = 'RER1'
    -> LIMIT  100,10;
+----+-------------+------------+--------+----------------------+---------+---------+---------------------+-------+-------------+
| id | select_type | table      | type   | possible_keys        | key     | 
key_len | ref                 | rows  | Extra       |
+----+-------------+------------+--------+----------------------+---------+---------+---------------------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                 | NULL    | 
NULL    | NULL                |  7354 |             |
|  1 | PRIMARY     | lists      | eq_ref | PRIMARY,acct_list_ID | PRIMARY | 
6       | t.acct_ID,t.list_ID |     1 | Using where |
|  2 | DERIVED     | lists      | index  | NULL                 | PRIMARY | 
6       | NULL                | 23508 | Using index |
+----+-------------+------------+--------+----------------------+---------+---------+---------------------+-------+-------------+
3 rows in set (0.01 sec)

mysql> SELECT list_ID, list_Name, acct_ID, list_Qty
    -> FROM   lists
    ->        INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
    ->                    FROM   lists
    ->                    GROUP BY acct_id
    ->                    ) AS t
    ->        USING (acct_ID, list_ID)
    -> WHERE  list_Active = 'Yes'
    ->        AND cpny_ID = 'RER1'
    -> LIMIT  100,10;
+---------+----------------------------+---------+----------+
| list_ID | list_Name                  | acct_ID | list_Qty |
+---------+----------------------------+---------+----------+
|       3 | Farm                       | BA8M    |        0 |
|      10 | Woodbury                   | BA8Y    |      100 |
|       2 | Brookview Heights 03-23-04 | BA9O    |      278 |
|       4 | Magnet Mailing             | BABA    |      250 |
|       2 | Fall Back                  | BABM    |      223 |
|       1 | Contact list               | BACF    |       71 |
|       4 | Friends/Family             | BAE2    |       10 |
|       1 | St. Michael                | BAE7    |      139 |
|       2 | JS Prospects               | BAE8    |      196 |
|       1 | Home Focus                 | BAE9    |       55 |
+---------+----------------------------+---------+----------+
10 rows in set (0.03 sec)

mysql> CREATE TEMPORARY TABLE `max_list_id` (
    ->    `acct_ID` CHAR (4) NOT NULL,
    ->    `list_ID` INT UNSIGNED DEFAULT '0' NOT NULL,
    ->     PRIMARY KEY(`acct_ID`)
    ->    );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO max_list_ID
    -> SELECT acct_ID, max(list_ID) AS list_ID
    -> FROM   lists
    -> GROUP BY acct_id;
Query OK, 7354 rows affected (0.23 sec)
Records: 7354  Duplicates: 0  Warnings: 0

mysql> EXPLAIN
    -> SELECT l.list_ID, l.list_Name, l.acct_ID, l.list_Qty
    -> FROM   lists AS l
    ->        INNER JOIN max_list_id AS t
    ->        USING (acct_ID, list_ID)
    -> WHERE  l.list_Active = 'Yes'
    ->        AND l.cpny_ID = 'RER1'
    -> LIMIT  100,10;
+----+-------------+-------+--------+----------------------+--------------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys        | key          | 
key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+----------------------+--------------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | l     | ref    | PRIMARY,acct_list_ID | acct_list_ID | 
4       | const               | 6060 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY              | PRIMARY      | 
4       | mailprint.l.acct_ID |    1 | Using where |
+----+-------------+-------+--------+----------------------+--------------+---------+---------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT l.list_ID, l.list_Name, l.acct_ID, l.list_Qty
    -> FROM   lists AS l
    ->        INNER JOIN max_list_id AS t
    ->        USING (acct_ID, list_ID)
    -> WHERE  l.list_Active = 'Yes'
    ->        AND l.cpny_ID = 'RER1'
    -> LIMIT  100,10;
+---------+----------------------------+---------+----------+
| list_ID | list_Name                  | acct_ID | list_Qty |
+---------+----------------------------+---------+----------+
|       3 | Farm                       | BA8M    |        0 |
|      10 | Woodbury                   | BA8Y    |      100 |
|       2 | Brookview Heights 03-23-04 | BA9O    |      278 |
|       4 | Magnet Mailing             | BABA    |      250 |
|       2 | Fall Back                  | BABM    |      223 |
|       1 | Contact list               | BACF    |       71 |
|       4 | Friends/Family             | BAE2    |       10 |
|       1 | St. Michael                | BAE7    |      139 |
|       2 | JS Prospects               | BAE8    |      196 |
|       1 | Home Focus                 | BAE9    |       55 |
+---------+----------------------------+---------+----------+
10 rows in set (0.00 sec)

mysql> DROP TABLE max_list_id;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //
mysql> DROP PROCEDURE max_ID//
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE PROCEDURE max_ID ()
    -> BEGIN
    -> CREATE TEMPORARY TABLE `max_list_id` (
    ->    `acct_ID` CHAR (4) NOT NULL,
    ->    `list_ID` INT UNSIGNED DEFAULT '0' NOT NULL,
    ->     PRIMARY KEY(`acct_ID`)
    ->    );
    ->
    -> INSERT INTO max_list_ID
    -> SELECT acct_ID, max(list_ID) AS list_ID
    -> FROM   lists
    -> GROUP BY acct_id;
    ->
    -> EXPLAIN
    -> SELECT l.list_ID, l.list_Name, l.acct_ID, l.list_Qty
    -> FROM   lists AS l
    ->        INNER JOIN max_list_id AS t
    ->        USING (acct_ID, list_ID)
    -> WHERE  l.list_Active = 'Yes'
    ->        AND l.cpny_ID = 'RER1'
    -> LIMIT  100,10;
    ->
    -> DROP TABLE max_list_id;
    -> END; //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL max_ID();
+----+-------------+-------+--------+----------------------+--------------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys        | key          | 
key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+----------------------+--------------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | l     | ref    | PRIMARY,acct_list_ID | acct_list_ID | 
4       | const               | 6060 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY              | PRIMARY      | 
4       | mailprint.l.acct_ID |    1 | Using where |
+----+-------------+-------+--------+----------------------+--------------+---------+---------------------+------+-------------+
2 rows in set (0.20 sec)

Query OK, 0 rows affected (0.20 sec)

mysql> DELIMITER //
mysql>
mysql> DROP PROCEDURE max_ID//
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE max_ID ()
    -> BEGIN
    -> CREATE TEMPORARY TABLE `max_list_id` (
    ->    `acct_ID` CHAR (4) NOT NULL,
    ->    `list_ID` INT UNSIGNED DEFAULT '0' NOT NULL,
    ->     PRIMARY KEY(`acct_ID`)
    ->    );
    ->
    -> INSERT INTO max_list_ID
    -> SELECT acct_ID, max(list_ID) AS list_ID
    -> FROM   lists
    -> GROUP BY acct_id;
    ->
    -> SELECT l.list_ID, l.list_Name, l.acct_ID, l.list_Qty
    -> FROM   lists AS l
    ->        INNER JOIN max_list_id AS t
    ->        USING (acct_ID, list_ID)
    -> WHERE  l.list_Active = 'Yes'
    ->        AND l.cpny_ID = 'RER1'
    -> LIMIT  100,10;
    ->
    -> DROP TABLE max_list_id;
    -> END; //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> CALL max_ID();
+---------+----------------------------+---------+----------+
| list_ID | list_Name                  | acct_ID | list_Qty |
+---------+----------------------------+---------+----------+
|       3 | Farm                       | BA8M    |        0 |
|      10 | Woodbury                   | BA8Y    |      100 |
|       2 | Brookview Heights 03-23-04 | BA9O    |      278 |
|       4 | Magnet Mailing             | BABA    |      250 |
|       2 | Fall Back                  | BABM    |      223 |
|       1 | Contact list               | BACF    |       71 |
|       4 | Friends/Family             | BAE2    |       10 |
|       1 | St. Michael                | BAE7    |      139 |
|       2 | JS Prospects               | BAE8    |      196 |
|       1 | Home Focus                 | BAE9    |       55 |
+---------+----------------------------+---------+----------+
10 rows in set (0.19 sec)

Query OK, 0 rows affected (0.19 sec)

mysql>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to