1

My question is regards the performance.

What exactly happening when you use group by and then limit?

I have a very big table, when I query with limit and without group by its immediate. When I use group by with limit 1 it takes forever. Now, the group by is running on an index, without the group by I get many potential "groups", items that will match the group by, so I don't need to worry there is only 1 group. So what I don't understand is why won't MySQL stop after the first group has been found when I put limit 1?

The query (even tried with force index):

SELECT fileso.hostId
FROM indicators.fileso fileso force index (hostId)
WHERE 1 = 1 
GROUP BY  fileso.hostId
LIMIT 1;

Without group by it's immediate.

Table Creation (large table):

CREATE TABLE `fileso2` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `hostId` INT(11) UNSIGNED NOT NULL COMMENT 'code',
    `sha256` BINARY(32) NOT NULL COMMENT 'meir',
    `fileName` VARCHAR(150) NULL DEFAULT NULL COMMENT 'meir',
    `fullPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `datein` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `lastSeen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `lastRA` TIMESTAMP NULL DEFAULT NULL COMMENT '?',
    `riskLevel` SMALLINT(6) NOT NULL DEFAULT '-1' COMMENT '?',
    `policiesViolated` VARCHAR(1000) NULL DEFAULT NULL,
    `NTFSOwner` VARCHAR(255) NULL DEFAULT NULL COMMENT 'meir',
    `NTFSOwnerId` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle -done',
    `p_runningUser` VARCHAR(255) NULL DEFAULT NULL COMMENT 'meir',
    `p_parentUser` VARCHAR(255) NULL DEFAULT NULL,
    `p_commandLineParams` VARCHAR(1000) NULL DEFAULT NULL COMMENT 'meir',
    `p_runningUserId` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle -done',
    `p_ParentPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `ar_regPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `ar_regKey` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
    `ar_regValue` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `s_serviceName` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
    `d_driverName` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
    `hostName` CHAR(20) NULL DEFAULT NULL,
    `extension` CHAR(5) NOT NULL DEFAULT '',
    `uniqueness` BINARY(32) NOT NULL DEFAULT '00000000000000000000000000000000',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uniqueness` (`uniqueness`),
    INDEX `p_runningUserId` (`p_runningUserId`),
    INDEX `riskLevel` (`riskLevel`),
    INDEX `lastSeen` (`lastSeen`),
    INDEX `hostId` (`hostId`),
    INDEX `datein` (`datein`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2384056;
5
  • 1
    Is there an ORDER BY, too?
    – dnoeth
    Mar 21, 2016 at 18:05
  • 1
    Please post the result of SHOW CREATE TABLE My_Table\G and also the text of your query.
    – Vérace
    Mar 21, 2016 at 18:10
  • no order by, added all the info
    – omri
    Mar 22, 2016 at 8:12
  • Please, don't give us a dumbed down query, give us a query that you need/use and makes some sense. The one you show is just useless, you can replace it with select host_id from indicators.fileso limit 1 ;. No GROUP BY needed and the problem is gone. Mar 22, 2016 at 9:13
  • And also add the version of MySQL you use. Mar 22, 2016 at 9:15

2 Answers 2

1

Short answer: You did not give enough info to answer your question.

Long answer: This may cover your situation, plus explain a lot of other things...

In MySQL, GROUP BY implies the same ORDER BY. (This is a non-standard extension.) (But this point may be irrelevant.)

GROUP BY can be performed in two ways. Please provide EXPLAIN SELECT ... for both and, if possible, EXPLAIN FORMAT=JSON SELECT ...; those may show the difference.

Plan A: Gather all the data; sort it; walk through the sorted list doing the "grouping" stuff, such as SUM, COUNT, MAX. (Actually there is a leapfrogging optimization for MAX.) This lengthy process is what happens without the LIMIT. Note that it gives ORDER BY for 'free'.

Plan B: Keep a hash in memory; scan through the table (without any sorting); the result is in the hash. Then it needs sorting. (ORDER BY NULL avoids this sort.)

Both A and B involve at least a full table scan, so they do not explain your "immediate" response unless the table is quite small. Is it?

Neither of those necessarily took advantage of any index. So...

Plan C: INDEX(x) and GROUP BY x. In this case, the optimizer may choose to use the index as a way to avoid the 'sort'. Furthermore if you have LIMIT 1, it only needs to do one value of x. (Note: If there is a 'sort' in the pipeline, LIMIT cannot be done until after the sort; by then most of work has been done, so the limit does not save much time.)

Plan D (already alluded to): If you are only doing MAX/MIN, then only the last/first row need be looked at. If you also have a relevant index, it can reach into the index for the last/first row in each clump, thereby skipping all the other rows. In this case, even without a LIMIT, you may get a significant performance boost.

Caveat

When doing SELECT x, y FROM t GROUP BY x, the engine is under no obligation to pick any particular value of y. Furthermore the Optimizer seems to treat this case as "not worth optimizing", and may not do you what you expect (nor what I say above). More on ONLY_FULL_GROUP_BY

6
  • Added all the info, but after reading your answer, still my main question is: If you group by indexed column and limit N, does the engine groups by the first N items he finds right?
    – omri
    Mar 22, 2016 at 9:36
  • Since you are going outside the definition of GROUP BY, all bets are off. (I added a Caveat.)
    – Rick James
    Mar 22, 2016 at 17:20
  • @RickJames Did they post something on a comment I missed? I see only SELECT x FROM t GROUP BY x LIMIT 1; in the question. No y (or any other columns). Mar 22, 2016 at 18:02
  • @ypercubeᵀᴹ - Doh! Your point it well taken.
    – Rick James
    Mar 22, 2016 at 19:54
  • @Rick James, you miss understand me. What I asked was whether the limit 1 will be hit immediately after grouping the first item or will the group by complete and only after that the limit will occur. cause thats the behavior I see in my initial query
    – omri
    Mar 23, 2016 at 12:41
0

I had the same question for sqlite. Yes, it did do the obvious optimization.

import sqlite3
import random

conn = sqlite3.connect('db.sqlite')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS my_table (
                    id INTEGER PRIMARY KEY,
                    other_id INTEGER
                )''')

for i in range(50000):
    other_id = random.randint(1, i+1)
    cursor.execute('''INSERT INTO my_table (other_id) VALUES (?)''', (other_id,))

conn.commit()
conn.close()

This

SELECT MAX(id), other_id
FROM my_table
GROUP BY other_id 
LIMIT 1;
-- Run Time: real 0.012 user 0.011253 sys 0.000632

is much faster than

SELECT MAX(id), other_id
FROM my_table
GROUP BY other_id;
-- Run Time: real 0.091 user 0.052810 sys 0.037161

EXPLAIN QUERY PLAN doesn't help answer the question:

EXPLAIN QUERY PLAN SELECT MAX(id), other_id
FROM my_table
GROUP BY other_id LIMIT 1;
QUERY PLAN
|--SCAN my_table
`--USE TEMP B-TREE FOR GROUP BY

but I could have figured it out from the VM instructions from EXPLAIN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.