Help Required – Slow Query – max tables

  • Resolved examsdaily

    (@examsdaily)


    5 years, 4 months ago

    Hey there,
    We were finding slow queries eating up our database over the past couple of days;
    Today after diagnosing the MYSQL queries concerning the load we found that the query that overloaded our database.
    Help us to bypass the same
    We do run a blog with more than 10k posts and equally same number of tables from thereon. We feel like the maybe our case does’nt throw importance on tables and hence essentially we would like to skip the tables from search if that is the only concern that remains..
    We love Tablepress Eternally..
    THanks in advance

    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE ‘%14%’) OR (wp_posts.post_excerpt LIKE ‘%14%’) OR (wp_posts.post_content LIKE ‘%14%’) OR (wp_posts.post_content REGEXP ‘\\[table id=([“\’]?)(47|112|120|122|128|129|130|134|140|155|184|214|220|221|222|223|226|228|240|241|242|245|247|248|249|250|264|302|329|330|355|365|409|422|423|424|425|433|440|441|443|444|445|450|462|464|465|466|469|472|474|475|476|477|485|486|487|489|493|497|501|502|503|506|507|511|512|518|528|538|539|540|547|555|557|569|572|573|584|585|586|590|591|592|593|595|597|598|599|600|601|602|603|604|605|606|610|612|613|615|617|622|625|626|627|628|629|630|632|633|634|635|636|638|640|644|646|647|648|649|650|654|655|657|658|659|660|663|666|667|668|671|672|673|674|676|677|678|681|682|683|684|685|688|692|693|694|696|697|698|699|713|714|715|717|718|721|725|727|728|734|736|740|743|744|745|747|754|756|759|760|767|768|774|779|780|785|788|789|790|791|792|797|798|799|806|813|815|818|822|825|827|828|829|832|834|835|836|837|838|839|845|846|847|848|852|853|855|858|859|860|864|865|867|869|870|871|875|876|877|879|880|883|884|885|887|888|891|892|895|896|897|907|908|909|910|911|912|913|914|915|916|917|918|919|921|927|929|931|934|936|937|947|957|973|974|988|1020|1034|1035|1067|1071|1074|1094|1098|1134|1185|1189|1195|1196|1197|1212|1215|1252|1258|1259|1262|1265|1267|1282|1283|1291|1294|1296|1297|1298|1301|1310|1315|1333|1339|1341|1348|1349|1350|1354|1370|1386|1388|1390|1393|1398|1406|1416|1431|1450|1451|1453|1488|1489|1494|1535|1540|1557|1558|1559|1560|1565|1595|1596|1603|1630|1637|1647|1670|1680|1690|1712|1715|1718|1719|1721|1733|1735|1737|1761|1815|1817|1833|1835|1838|1855|1870|1889|1907|1939|1941|1970|1975|1976|1990|1992|2005|2018|2021|2025|2029|2030|2041|2078|2079|2088|2123|2134|2136|2145|2175|2194|2195|2196|2197|2198|2199|2213|2227|2255|2272|2301|2302|2320|2326|2328|2330|2331|2332|2333|2347|2348|2356|2357|2366|2374|2378|2418|2419|2425|2437|2453|2454|2456|2465|2468|2493|2511|2522|2530|2540|2542|2579|2597|2615|2639|2640|2641|2642|2648|2651|2665|2675|2704|2731|2736|2769|2792|2793|2795|2813|2851|2873|2893|2899|2913|2920|2921|2922|2923|2936|2938|2946|2954|2956|2962|2973|3003|3007|3016|3018|3021|3023|3033|3044|3045|3061|3063|3080|3113|3128|3129|3139|3151|3154|3174|3193|3194|3196|3197|3198|3199|3200|3201|3202|3213|3223|3227|3231|3285|3307|3308|3312|3317|3322|3324|3331|3340|3395|3402|3406|3433|3437|3440|3467|3494|3495|3500|3503|3517|3525|3533|3553|3554|3564|3566|3567|3571|3584|3585|3604|3616|3617|3633|3641|3655|3656|3672|3683|3699|3706|3707|3709|3710|3712|3714|3716|3720|3731|3735|3757|3760|3762|3766|3767|3787|3789|3812|3817|3830|3832|3850|3853|3862|3869|3872|3874|3885|3889|3894|3900|3906|3934|3949|3950|3961|3963|3964|3970|3976|3977|3978|4000|4001|4009|4017|4031|4032|4033|4036|4047|4087|4092|4101|4108|4120|4132|4137|4205|4206|4282|4292|4296|4299|4307|4313|4318|4346|4357|4375|4394|4450|4454|4457|4467|4475|4511|4519|4529|4538|4542|4552|4566|4582|4589|4593|4601|4606|4667|4672|4675|4709|4710|4719|4730|4735|4757|4764|4773|4846|4867|4881|4892|4943|4971|5055|5091|5096|5107|5119|5140|5181|5224|5321)([\]”\’ /])’)) AND ((wp_posts.post_title LIKE ‘%July%’) OR (wp_posts.post_excerpt LIKE ‘%July%’) OR (wp_posts.post_content LIKE ‘%July%’) OR (wp_posts.post_content REGEXP ‘\\[table id=([“\’]?)(248|380|381|433|440|505|515|555|569|573|585|592|593|594|596|598|601|603|604|605|608|610|613|615|622|625|627|628|629|632|635|636|638|640|643|644|647|656|658|659|666|668|671|672|674|677|678|681|683|714|730|740|757|758|765|787|788|789|797|822|825|832|833|834|836|838|839|843|848|852|853|855|856|858|859|860|865|866|867|870|871|872|875|876|877|879|880|883|884|888|896|909|911|915|919|925|929|934|936|947|1056|1071|1072|1074|1077|1109|1262|1298|1315|1343|1348|1453|1482|1557|1558|1559|1560|1563|1565|1637|1694|1707|1716|1830|1833|1835|1883|1921|1966|1989|1990|2131|2186|2193|2195|2196|2197|2198|2328|2530|2540|2542|2742|3285|3434|3534|3604|3723|3733|3789|3859|4123|4353|4359|4422|4466|4479|4518|4540|4566|4568|4586|4615|4651|4808|4861|4863|4870|4871|4877|4880|4955|5096|5107|5119|5151|51 … ( truncated to the first 4KB )

    https://drive.google.com/open?id=1BrMcVMuxPs81ZIWW5R8l6WNwju8WuMCa
    https://drive.google.com/open?id=1Phlm-M5tfar_BSkMRqnbFCLTFc89opfa

    The page I need help with: [log in to see the link]

Viewing 9 replies - 1 through 9 (of 9 total)
  • Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    thanks for your post, and sorry for the trouble.

    This SQL query is part of the TablePress integration into the WordPress search, and can unfortunately happen if someone searches for a search term that appears in very many tables.
    Unfortunately, the only real solution right now that I can think of would be to turn off TablePress’ integration into the WordPress search. For that, please add this to the “functions.php” file of your theme or a small custom plugin:

    add_filter( 'tablepress_wp_search_integration', '__return_false' );

    Regards,
    Tobias

    Thread Starter examsdaily

    (@examsdaily)

    Thanks for the quick reply Tobias

    But we already have that filter in place;
    yet not uncommonly we could see that long query clogging our database.

    If you have any suggestions,
    Please do help us.

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    that would be really strange. As you can see in the code at https://github.com/TobiasBg/TablePress/blob/master/controllers/controller-frontend.php#L63 , if you use that filter, TablePress does not modify the search query…

    Regards,
    Tobias

    Thread Starter examsdaily

    (@examsdaily)

    Will look into this matter

    Thanks

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    did you find out more about this?

    Regards,
    Tobias

    Thread Starter examsdaily

    (@examsdaily)

    Thanks, for your interest in this thread,

    From the query log monitoring, we could still see some queries run resembling the following. Hence at time posts unnecessary load on the database server.

    Unfortunate that we are still looking for the cause and caller, perhaps attributed to the search function.

    
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND (((wp_posts.post_title LIKE '%Ib%') OR (wp_posts.post_excerpt LIKE '%Ib%') OR (wp_posts.post_content LIKE '%Ib%') OR (wp_posts.post_content REGEXP '\\[table id=(["\']?)(38|81|103|107|108|117|126|137|143|155|159|161|162|167|173|174|184|189|192|220|223|228|240|241|245|250|379|398|417|422|433|440|443|444|445|453|464|465|469|472|473|474|475|476|477|485|495|496|502|503|506|508|509|515|518|531|584|586|592|593|594|596|600|603|606|607|610|612|627|628|644|645|655|656|660|668|673|674|685|688|696|698|699|710|724|726|746|774|779|782|783|784|788|789|790|791|792|794|797|798|799|815|822|825|827|836|839|840|841|845|850|855|864|870|871|877|879|885|890|892|895|897|898|904|906|914|917|918|919|936|952|961|962|968|975|1003|1023|1029|1037|1045|1049|1057|1128|1146|1197|1212|1255|1256|1257|1262|1264|1281|1289|1294|1296|1297|1316|1317|1330|1341|1348|1398|1424|1439|1445|1450|1451|1452|1455|1479|1489|1500|1522|1530|1533|1553|1565|1627|1641|1647|1670|1681|1698|1700|17

    — query goes on

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    that’s really strange. I have no clue where those are coming from then, sorry.

    Regards,
    Tobias

    Thread Starter examsdaily

    (@examsdaily)

    We resolved the issue a month ago.

    Turns out the add filter query was overridden by a plugin for mobile theme.

    And the nasty effects of database clogging was mitigated by adding the filter
    as a custom plugin ( previously we pasted the same to function.php – effective only for desktop vies )

    Thanks for helping us out.

    Also let me make this an opportunity to request a tick-box within plugin
    itself in future releases, that gives an option to exclude the tables from search queries. In our case,our tb-tables scaled enormously to some 10000 tables in tablepress and a single search query had the power to derail the database for couple of minutes.

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    good to hear that you found the cause in the mobile theme here!

    I’m not sure that the tick-box is a good solution, to be honest. It will be a rather confusing setting for most users. I’ll however investigate other options for speeding up the search here!

    Regards,
    Tobias

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Help Required – Slow Query – max tables’ is closed to new replies.