Mysql – Selecting N values from each group

greatest-n-per-groupMySQLmysql-5.7

I have 2 tables which have to be joined to get needed data.

Table1 is articles, Table2 is for channels/categories.

For the sake of simplicity, both tables only have 2 fields.

Basically, I need to select N unique IDs for each group (term_id). Values should't repeat between groups.

I can achieve expected results with RANK and PARTITION but it only works with MySQL 8.x, while I need it to work in 5.7.

Fiddle with the table structure, some sample data and current query I have is here

How can this approach be adapted to 5.7 MySQL?

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
CREATE TABLE `Table1` (
    `ID` BIGINT(20) UNSIGNED NOT NULL,
    `date` DATETIME NOT NULL);
CREATE TABLE `Table2` (
    `object_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `term_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0'
);

INSERT INTO `Table1` (`ID`, `date`) VALUES (195512, '2019-02-27 12:28:18'),
VALUES (195260, '2017-01-02 15:26:45'),
VALUES (195245, '2019-01-02 14:22:57'),
VALUES (192628, '2018-12-20 13:44:39'),
VALUES (192563, '2018-12-20 10:53:32'),
VALUES (191224, '2018-12-14 15:32:22'),
VALUES (189468, '2018-12-07 10:25:31'),
VALUES (188520, '2018-12-03 14:59:59'),
VALUES (187808, '2018-11-30 14:03:27'),
VALUES (185682, '2018-11-20 21:20:18'),
VALUES (195473, '2017-01-03 10:52:01'),
VALUES (192982, '2018-12-21 19:55:42'),
VALUES (192695, '2018-12-20 15:22:41'),
VALUES (192611, '2017-12-20 13:19:29'),
VALUES (192320, '2018-12-19 14:06:06'),
VALUES (192026, '2018-12-18 12:16:22'),
VALUES (191218, '2018-12-14 14:37:57'),
VALUES (190677, '2018-12-12 13:22:07'),
VALUES (190661, '2018-12-12 13:08:27'),
VALUES (190147, '2018-12-10 12:43:42'),
VALUES (195260, '2019-01-02 15:26:45'),
VALUES (192676, '2018-12-20 15:30:08'),
VALUES (192409, '2018-12-19 15:44:59'),
VALUES (192057, '2018-12-18 14:46:46'),
VALUES (191825, '2018-12-17 15:21:13'),
VALUES (190968, '2018-12-13 15:59:28'),
VALUES (190701, '2018-12-12 15:21:18'),
VALUES (190450, '2018-12-11 16:03:07'),
VALUES (190188, '2018-12-10 15:08:08'),
VALUES (189484, '2018-12-07 15:43:13'),
VALUES (195295, '2019-01-02 15:23:54'),
VALUES (192876, '2018-12-21 13:25:22'),
VALUES (192537, '2018-12-20 11:59:50'),
VALUES (192359, '2018-12-19 13:48:19'),
VALUES (192050, '2018-12-18 13:18:03'),
VALUES (192059, '2018-12-18 13:03:10'),
VALUES (192051, '2018-12-18 13:00:17'),
VALUES (191581, '2018-12-17 12:00:12'),
VALUES (191260, '2018-12-14 15:55:44'),
VALUES (190729, '2018-12-12 15:12:36'),



INSERT INTO `Table2` (`object_id`, `term_id`) VALUES 
(195295, 568),
(192876, 568),
(192537, 568),
(192359, 568),
(192050, 568),
(192059, 568),
(192051, 568),
(191581, 568),
(191260, 568),
(190729, 568),
(190387, 568),
(190349, 568),
(189122, 568),
(188751, 568),
(187831, 568),
(187273, 568),
(187020, 568),
(187025, 568),
(185320, 568),
(185283, 568),
(195512, 1)  ,
(195260, 1)  ,
(195245, 1)  ,
(192628, 1)  ,
(192563, 1)  ,
(191224, 1)  ,
(189468, 1)  ,
(188520, 1)  ,
(187808, 1)  ,
(185682, 1)  ,
(183886, 1)  ,
(182668, 1)  ,
(182566, 1)  ,
(182194, 1)  ,
(180177, 1)  ,
(179738, 1)  ,
(179181, 1)  ,
(176889, 1)  ,
(176862, 1)  ,
(175258, 1)  ,
(195473, 564),
(192982, 564),
(192695, 564),
(192611, 564),
(192320, 564),
(192026, 564),
(191218, 564),
(190677, 564),
(190661, 564),
(190147, 564),
(189468, 564),
(189190, 564),
(189159, 564),
(189062, 564),
(188732, 564),
(188688, 564),
(188666, 564),
(188609, 564),
(188611, 564),
(188613, 564),
(195260, 91170),
(192676, 91170),
(192409, 91170),
(192057, 91170),
(191825, 91170),
(190968, 91170),
(190701, 91170),
(190450, 91170),
(190188, 91170),
(189484, 91170),
(189224, 91170),
(189011, 91170),
(188716, 91170),
(188522, 91170),
(187585, 91170),
(187297, 91170),
(187094, 91170),
(186788, 91170),
(185769, 91170),
(185577, 91170);

Query:

SELECT `ID`, `term_id`, `date`, `rank`
FROM 
(
    SELECT DISTINCT(`ID`), `term_id`, `date`, RANK() OVER (PARTITION BY `channels`.`term_id` ORDER BY FIELD(`channels`.`term_id`, 1, 564, 91170, 568 ), `main`.`date` DESC) AS `rank`
    FROM `Table1` AS `main`
    INNER JOIN `Table2` AS `channels` ON(`channels`.`object_id` = `main`.`ID`) AND `channels`.`term_id` IN (1, 564, 91170, 568) 
    GROUP BY (`ID`)
) AS x 
WHERE `rank` <= 3

Expected Result:

| ID     | term_id | date                | rank |
|--------|---------|---------------------|------|
| 195512 | 1       | 2019-02-27 12:28:18 | 1    |
| 195245 | 1       | 2019-01-02 14:22:57 | 2    |
| 192628 | 1       | 2018-12-20 13:44:39 | 3    |
| 192982 | 564     | 2018-12-21 19:55:42 | 1    |
| 192695 | 564     | 2018-12-20 15:22:41 | 2    |
| 192320 | 564     | 2018-12-19 14:06:06 | 3    |
| 195295 | 568     | 2019-01-02 15:23:54 | 1    |
| 192876 | 568     | 2018-12-21 13:25:22 | 2    |
| 192537 | 568     | 2018-12-20 11:59:50 | 3    |
| 192676 | 91170   | 2018-12-20 15:30:08 | 1    |
| 192409 | 91170   | 2018-12-19 15:44:59 | 2    |
| 192057 | 91170   | 2018-12-18 14:46:46 | 3    |

Best Answer

Functionally equivalent but not very efficient way is the next:

SELECT q.*
  FROM ( SELECT z.* 
              , IF( @prev_tid = (@prev_tid := term_id)
                  , @cnt := @cnt + 1                  
                  , @cnt := 1                                 
                  ) AS rank
           FROM ( SELECT DISTINCT(id)
                       , term_id
                       , `date`
                    FROM table1 AS m
                    JOIN table2 AS c ON c.object_id = m.id
                                    AND c.term_id IN (1, 564, 91170, 568) 
                   ORDER BY c.term_id ASC
                          , m.date DESC
                ) AS z
          WHERE @cnt := 1                  
            AND (@prev_tid := 0) OR 1     
          ORDER BY term_id ASC
                 , date DESC
       ) AS q
 WHERE q.rank <= 3
 ;

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

MySQL Performance – Why Simple SELECTs on InnoDB Are 100x Slower Than on MyISAM

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4