WordPress.org

Make WordPress Core

Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#22074 closed enhancement (fixed)

Very Large Query that Crashes the whole My SQL and site

Reported by: adest Owned by: wonderboymusic
Milestone: 3.7 Priority: normal
Severity: normal Version: 3.4.1
Component: Performance Keywords: has-patch needs-unit-tests
Focuses: Cc:

Description (last modified by SergeyBiryukov)

Hi David,

The problem is being caused by an extremely large mysql query. This is just a snippet of it. The full query is in /home/profithunters/query. You should have your dev look at it.

What user/pass are you using for mysql? You can login as the root mysql user by logging into https://profithunters.nmsrv.com/gadmin/ and then going to MySQL -> Launch phpmyadmin.

       | 370           | 371       |
| 1697 | profithu_wrdp2 | localhost | profithu_wrdp2 | Query   | 78   | statistics           | SELECT * FROM wp_posts  WHERE (post_type = 'page' AND post_status = 'publish')  AND ( ID <> 3778  AND ID <> 49911  AND ID <> 0  AND ID <> 20531  AND ID <> 3776  AND ID <> 3792  AND ID <> 3794  AND ID <> 3797  AND ID <> 3799  AND ID <> 3801  AND ID <> 3803  AND ID <> 3805  AND ID <> 3807  AND ID <> 3811  AND ID <> 5540  AND ID <> 6500  AND ID <> 8965  AND ID <> 9363  AND ID <> 10974  AND ID <> 11516  AND ID <> 11822  AND ID <> 12903  AND ID <> 12907  AND ID <> 12911  AND ID <> 13928  AND ID <> 13930  AND ID <> 14188  AND ID <> 14192  AND ID <> 14935  AND ID <> 14937  AND ID <> 14939  AND ID <> 14941  AND ID <> 15023  AND ID <> 15376  AND ID <> 19768  AND ID <> 20980  AND ID <> 20984  AND ID <> 21353  AND ID <> 21550  AND ID <> 21554  AND ID <> 21559  AND ID <> 21562  AND ID <> 21618  AND ID <> 21623  AND ID <> 21629  AND ID <> 21674  AND ID <> 21677  AND ID <> 21682  AND ID <> 21689  AND ID <> 21694  AND ID <> 21703  AND ID <> 21706  AND ID <> 21707  AND ID <> 21714  AND ID <> 21717  AND ID <> 21744  AND ID <> 21834  AND ID <> 21859  AND ID <> 21862  AND ID <> 21865  AND ID <> 21873  AND ID <> 21876  AND ID <> 21880  AND ID <> 21884  AND ID <> 21897  AND ID <> 21900  AND ID <> 21902  AND ID <> 21905  AND ID <> 21936  AND ID <> 21938  AND ID <> 21940  AND ID <> 21949  AND ID <> 21951  AND ID <> 21958  AND ID <> 21966  AND ID <> 21983  AND ID <> 21986  AND ID <> 21989  AND ID <> 21995  AND ID <> 22000  AND ID <> 22004  AND ID <> 22006  AND ID <> 22010  AND ID <> 22014  AND ID <> 22016  AND ID <> 22020  AND ID <> 22024  AND ID <> 22026  AND ID <> 22028  AND ID <> 22030  AND ID <> 22037  AND ID <> 22040  AND ID <> 22043  AND ID <> 22046  AND ID <> 22049  AND ID <> 22058  AND ID <> 22060  AND ID <> 22062  AND ID <> 22065  AND ID <> 22068  AND ID <> 22071  AND ID <> 22073  AND ID <> 22075  AND ID <> 22078  AND ID <> 22085  AND ID <> 22087  AND ID <> 22089  AND ID <> 22094  AND ID <> 22125  AND ID <> 22128  AND ID <> 22133  AND ID <> 22137  AND ID <> 22141  AND ID <> 22144  AND ID <> 22151  AND ID <> 22153  AND ID <> 22155  AND ID <> 22157  AND ID <> 22160  AND ID <> 22162  AND ID <> 22165  AND ID <> 22168  AND ID <> 22170  AND ID <> 22172  AND ID <> 22174  AND ID <> 22176  AND ID <> 22178  AND ID <> 22180  AND ID <> 22184  AND ID <> 22186  AND ID

Attachments (5)

use-in-query.diff (1.5 KB) - added by wonderboymusic 6 years ago.
test-get-pages-inc-exc.diff (921 bytes) - added by wonderboymusic 6 years ago.
post.diff (1.2 KB) - added by mikelopez 5 years ago.
Recommended patch
22074.diff (1.5 KB) - added by wonderboymusic 5 years ago.
22074.2.diff (4.8 KB) - added by wonderboymusic 5 years ago.

Download all attachments as: .zip

Change History (23)

#1 follow-up: @nacin
6 years ago

We'd have to see the rest of the query, but, that looks like some custom code which calls get_pages() with a very lengthy 'exclude' parameter. We could optimize it a bit with an IN, but it's still probably going to be a long query.

#2 @wonderboymusic
6 years ago

  • Keywords has-patch needs-unit-tests added
  • Milestone changed from Awaiting Review to 3.5

Include and exclude both need love - the current impl is bad. Will write unit tests.

#3 @wonderboymusic
6 years ago

attached test

#4 @scribu
6 years ago

  • Severity changed from critical to normal
  • Type changed from defect (bug) to enhancement

#5 follow-up: @ryan
6 years ago

Isn't this where we usually say get_pages() should use WP_Query?

#6 in reply to: ↑ 5 @nacin
6 years ago

Replying to ryan:

Isn't this where we usually say get_pages() should use WP_Query?

Yep. But WP_Query doesn't (yet) have support for child_of or exclude_tree.

#7 @helenyhou
6 years ago

  • Keywords punt added

#8 @markjaquith
6 years ago

  • Milestone changed from 3.5 to Future Release

To much potential for breakage this late in the cycle.

#9 @markjaquith
6 years ago

  • Keywords punt removed

#10 @wonderboymusic
5 years ago

  • Milestone changed from Future Release to 3.6

#11 @wonderboymusic
5 years ago

#18840 was marked as a duplicate.

#12 @SergeyBiryukov
5 years ago

#23459 was marked as a duplicate.

#13 @SergeyBiryukov
5 years ago

  • Description modified (diff)

#14 in reply to: ↑ 1 @mikelopez
5 years ago

Replying to nacin:

We'd have to see the rest of the query, but, that looks like some custom code which calls get_pages() with a very lengthy 'exclude' parameter. We could optimize it a bit with an IN, but it's still probably going to be a long query.

A long IN query is still better (way better actually) than a long AND query.

@mikelopez
5 years ago

Recommended patch

#15 @ryan
5 years ago

  • Milestone changed from 3.6 to Future Release

@wonderboymusic
5 years ago

#16 @wonderboymusic
5 years ago

  • Milestone changed from Future Release to 3.7

22074.diff​ is reboot of my original patch against trunk

#17 @wonderboymusic
5 years ago

  • Owner set to wonderboymusic
  • Resolution set to fixed
  • Status changed from new to closed

In 25168:

Improve the include / exclude SQL generation in get_pages() by using IN and NOT IN where applicable. Adds unit tests for include / exclude.

Fixes #22074.

#18 @wonderboymusic
5 years ago

In 25169:

No need to re-map the return values of wp_parse_id_list() to int.

See #22074, #11823.

Note: See TracTickets for help on using tickets.