Mysql subquery long work

Question

I have next mysql query

SELECT DISTINCT * FROM dom_small WHERE count>=0 AND dom NOT IN 
(SELECT dom FROM dom_small WHERE was_blocked=1) 
ORDER BY count DESC LIMIT 30 OFFSET 4702020

When i increase OFFSET more and more, subquery run long and long.

When OFFSET 0 mysql query load 0 sec but when 4702020 mysql query load 1 min 19,49 sec

How to solve this problem?

Thinks about it, it has to select all and then sort all and then seek forward `4702020` rows and then deliver you 30 rows from there. Of course it runs longer when you increase the offset — RiggsFolly, Jul 18 '16 at 10:14
duplicate of http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down — George G, Jul 18 '16 at 10:30

score 0 · Answer 1 · answered Jul 18 '16 at 10:23

0

You can yield same result with out using subquery

SELECT DISTINCT * FROM dom_small WHERE count>=0 AND  was_blocked=1
ORDER BY count DESC LIMIT 30 OFFSET 4702020;

answered Jul 18 '16 at 10:23

AssenKhan

576
5
15

2

I think @Dmitriy Kupriyanov wants result which is not in was_blocked=1. So your answer in incorrect. – Abhay Jul 18 '16 at 11:02
Think right, but in this case i get the equal time for all query when offset 0 or 4702020. Approximately 7 secons. It is cause why i use subquery. – Dmitriy Kupriyanov Jul 18 '16 at 12:01
equal time with incorrect results is still failure. If dom "A" has 25 entries and 1 of them is marked as blocked, then the user does not want to see dom "A" at all regardless of the 24 that actually were not originally blocked. – DRapp Jul 18 '16 at 12:20

score 0 · Answer 2 · answered Jul 18 '16 at 11:04

0

Use following query:

SELECT DISTINCT * FROM dom_small WHERE count>=0 AND dom NOT IN 
  (select * from(SELECT dom FROM dom_small WHERE was_blocked=1) t1 ) 
  ORDER BY count DESC LIMIT 30 OFFSET 4702020

It can speed up the performance by caching the sub-query result. I previously used this method and it helped me much.

But as others mentioned using offset with big numbers slow down the performance.

answered Jul 18 '16 at 11:04

Mostafa Vatanpour

1,328
13
18

Thank for you idea but 30 rows in set (2 min 5,96 sec) – Dmitriy Kupriyanov Jul 18 '16 at 12:14
@DmitriyKupriyanov thus I think optimizing the sub-query can not improve the performance. – Mostafa Vatanpour Jul 18 '16 at 12:20

score 0 · Answer 3 · answered Jul 18 '16 at 11:28

SELECT DISTINCT * FROM dom_small WHERE count>=0 AND dom NOT IN (SELECT dom FROM dom_small WHERE was_blocked=1) ORDER BY count DESC LIMIT 30 OFFSET 4702020

Although the other comments are accurate, the only suggestion I can offer is that your distinct and subquery are from the same table "dom_small". Also, you are not doing any aggregate count(*) vs what appears to be an actual column in your table called count.

That said, I would have an index to help optimize the query on

( dom, was_blocked, count )

Mysql subquery long work

3 Answers3