Get all rows where specific column value occurs more than once (filter out single occurences) I am trying to filter all sessionIds that occur once out of an existing result set.
This query is being used in a web application and runs on a big dataset (~ 35 million rows), so I want to prevent having subqueries here.
I tried this, which provides a filtered result, except that I now only get one row for each sessionId (and I want every request and response):
CREATE TABLE `api_log` (
  `id` varchar(50) NOT NULL,
  `clientId` varchar(100) DEFAULT NULL,
  `inserted` int(11) DEFAULT NULL,
  `sessionId` mediumtext DEFAULT NULL,
  `stage` varchar(120) DEFAULT NULL,
  `request` longtext CHARACTER SET utf8mb4 DEFAULT NULL,
  `response` longtext CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO api_log
  VALUES
    (&quot;1&quot;, &quot;abc&quot;, 1621008484, &quot;session1&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;2&quot;, &quot;abc&quot;, 1621008494, &quot;session2&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;3&quot;, &quot;abc&quot;, 1621008584, &quot;session1&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;4&quot;, &quot;abc&quot;, 1621008684, &quot;session2&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;5&quot;, &quot;abc&quot;, 1621008784, &quot;session3&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;6&quot;, &quot;abc&quot;, 1621008884, &quot;session4&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;7&quot;, &quot;abc&quot;, 1621008984, &quot;session5&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;8&quot;, &quot;abc&quot;, 1621009084, &quot;session6&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;9&quot;, &quot;abc&quot;, 1621009184, &quot;session7&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}'),
    (&quot;10&quot;, &quot;abc&quot;, 1621009284, &quot;session8&quot;, &quot;production&quot;, '{&quot;key&quot;:&quot;value&quot;}', '{&quot;key&quot;:&quot;value&quot;}');

SELECT
    `clientId`,
    `sessionId`,
    `inserted`,
    `stage`,
    `request`,
    `response`
FROM
    `api_log`
WHERE
    (stage = 'production') 
    AND (clientId = 'abc') 
    AND (
        `inserted` BETWEEN 1621008482 AND 1621009285
    )
GROUP BY
    `clientId`,
    `stage`,
    `sessionId`
HAVING
    COUNT(sessionId) &gt; 1

Is there any trick to get all rows where a sessionId occurs more than once?
In this case, I get two rows, one for session1 and one for session2, but I am missing two more because both mentioned sessionIds have an additional row that should match.
SQL fiddle: http://sqlfiddle.com/#!9/f87bb54/3
The first approach is to create an index by sessionId. But its datatype is MEDIUMTEXT...
So the approach is: normalize your data, move sessionId values into separate table and refer to it by foreign key. The reference column will be compact (4 or 8 bytes) and indexed, so its usage in the query as GROUP BY expression will improve.
The columns clientID and stage seems to be candidates to such normalization too.