Very Large Query that Crashes the whole My SQL and site

[adest]

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

[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.

[wonderboymusic]

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

[wonderboymusic]

attached test

[ryan]

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

[nacin]

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.

[markjaquith]

To much potential for breakage this late in the cycle.

[wonderboymusic]

#18840 was marked as a duplicate.

[SergeyBiryukov]

#23459 was marked as a duplicate.

[mikelopez]

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]

Recommended patch

[wonderboymusic]

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

[wonderboymusic]

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.

[wonderboymusic]

In 25169:

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

See #22074, #11823.